sunn789
چهارشنبه 01 مرداد 1393, 11:54 صبح
سلام
یه Sp دارم بااین اوصاف(البته اولین بار هست که Sp مینویسم)
سه تا سوال اول اینکه اگر در ساختار اشکال میبینید اساتید کمک بفرمایند خوشحال میشم
دوم اینکه چرا روی
@memberTotal = @MemberShoAmountPay + @membershipDebtPay
خطا میده
و سوم اینکه Datetime رو اگر بخوام تاریخ امروز بخوره چه بنویسم
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Sunny
-- Create date: 7/22/2014
-- Description: دریافت اطلاعات وام با استفاده از شماره عضو
-- =============================================
CREATE PROCEDURE GetVamDataByMemberID1
-- Add the parameters for the stored procedure here
@MemberID int = 0,
@MemberShoAmountPay int ,
@membershipDebtPay int,
@memberTotal int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN
-- Insert statements for procedure here
(SELECT @MemberShoAmountPay = sum( tblMembershipAmount.MembershipPay) from tblMembershipAmount
where tblMembershipAmount.MemberID = @MemberID)
(SELECT @membershipDebtPay = sum(tblReimburseDeptPay.ReimburseDeptPay) from tblReimburseDeptPay
where tblReimburseDeptPay.memberID = @MemberID)
@memberTotal = @MemberShoAmountPay + @membershipDebtPay
if (@memberTotal >0 )
Begin
INSERT INTO [dbo].[tbl-New-CheckOut]
([MemberId]
,[MembernationalCode]
,[Creator]
,[Debtor]
,[CheckOutDate]
,[Description])
VALUES
(@MemberID,
(select NationalCode from tblMembers where ID = @MemberID),
@memberTotal,
0,
GETDATE(),
'new')
end
if (@memberTotal <0 )
Begin
INSERT INTO [dbo].[tbl-New-CheckOut]
([MemberId]
,[MembernationalCode]
,[Creator]
,[Debtor]
,[CheckOutDate]
,[Description])
VALUES
(@MemberID,
(select NationalCode from tblMembers where ID = @MemberID),
0,
@memberTotal,
GETDATE(),
'new')
end
END
GO
یه Sp دارم بااین اوصاف(البته اولین بار هست که Sp مینویسم)
سه تا سوال اول اینکه اگر در ساختار اشکال میبینید اساتید کمک بفرمایند خوشحال میشم
دوم اینکه چرا روی
@memberTotal = @MemberShoAmountPay + @membershipDebtPay
خطا میده
و سوم اینکه Datetime رو اگر بخوام تاریخ امروز بخوره چه بنویسم
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Sunny
-- Create date: 7/22/2014
-- Description: دریافت اطلاعات وام با استفاده از شماره عضو
-- =============================================
CREATE PROCEDURE GetVamDataByMemberID1
-- Add the parameters for the stored procedure here
@MemberID int = 0,
@MemberShoAmountPay int ,
@membershipDebtPay int,
@memberTotal int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN
-- Insert statements for procedure here
(SELECT @MemberShoAmountPay = sum( tblMembershipAmount.MembershipPay) from tblMembershipAmount
where tblMembershipAmount.MemberID = @MemberID)
(SELECT @membershipDebtPay = sum(tblReimburseDeptPay.ReimburseDeptPay) from tblReimburseDeptPay
where tblReimburseDeptPay.memberID = @MemberID)
@memberTotal = @MemberShoAmountPay + @membershipDebtPay
if (@memberTotal >0 )
Begin
INSERT INTO [dbo].[tbl-New-CheckOut]
([MemberId]
,[MembernationalCode]
,[Creator]
,[Debtor]
,[CheckOutDate]
,[Description])
VALUES
(@MemberID,
(select NationalCode from tblMembers where ID = @MemberID),
@memberTotal,
0,
GETDATE(),
'new')
end
if (@memberTotal <0 )
Begin
INSERT INTO [dbo].[tbl-New-CheckOut]
([MemberId]
,[MembernationalCode]
,[Creator]
,[Debtor]
,[CheckOutDate]
,[Description])
VALUES
(@MemberID,
(select NationalCode from tblMembers where ID = @MemberID),
0,
@memberTotal,
GETDATE(),
'new')
end
END
GO