Reputation: 117
I'm noob at stored procedures, I have created a stored procedure that take some parameters from C# page, then send back result as OUTPUT
parameter.
I need to do some calculation to get end date, so I end up using a lot of IF statements. However, when I create the stored procedure I get error that I didn't know how to solve, every thing seems correct!.
Here is the stored procedure code:
CREATE PROCEDURE sp_RenewSubscription
-- Add the parameters for the stored procedure here
@Reference nvarchar(100),
@SubscribtionID nvarchar(100),
@Months int,
@Result nvarchar(200) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @EndDate as nvarchar;
DECLARE @MonthCounts as int;
IF NOT EXISTS(SELECT [Reference] FROM [Norton].[dbo].[SubscriptionStatus] WHERE [Reference] = @Reference)
SET @Result = '0: Reference ID not found'
ELSE
IF NOT EXISTS(SELECT [Reference] FROM [Norton].[dbo].[SubscriptionStatus] WHERE [Reference] = @Reference AND [SubscribtionID] = @SubscribtionID)
SET @Result = '0: Subscribtion ID not found'
ELSE
BEGIN
SELECT TOP 1 @EndDate = [EndDate], @MonthCounts = [SubscriptionMonthCount] FROM [Norton].[dbo].[SubscriptionStatus] WHERE [Reference] = @Reference AND [SubscribtionID] = @SubscribtionID
IF @EndDate = '0'
BEGIN
UPDATE [Norton].[dbo].[SubscriptionStatus]
SET [SubscriptionMonthCount] = @Months + @MonthCounts
WHERE [Reference] = @Reference AND [SubscribtionID] = @SubscribtionID
END
ELSE
BEGIN
UPDATE [Norton].[dbo].[SubscriptionStatus]
SET [SubscriptionMonthCount] = @Months
WHERE [Reference] = @Reference AND [SubscribtionID] = @SubscribtionID
END
SET @Result = '1: Done Successfully'
END
GO
END
GO
This is error I got:
Msg 102, Level 15, State 1, Procedure sp_RenewSubscription, Line 44
Incorrect syntax near 'END'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'END'.*
Thanks,
Upvotes: 1
Views: 237
Reputation: 4137
Try this:
CREATE PROCEDURE Sp_renewsubscription
-- Add the parameters for the stored procedure here
@Reference NVARCHAR(100),
@SubscribtionID NVARCHAR(100),
@Months INT,
@Result NVARCHAR(200) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET nocount ON;
DECLARE @EndDate AS NVARCHAR;
DECLARE @MonthCounts AS INT;
IF NOT EXISTS(SELECT [reference]
FROM [Norton].[dbo].[subscriptionstatus]
WHERE [reference] = @Reference)
SET @Result = '0: Reference ID not found'
ELSE IF NOT EXISTS(SELECT [reference]
FROM [Norton].[dbo].[subscriptionstatus]
WHERE [reference] = @Reference
AND [subscribtionid] = @SubscribtionID)
SET @Result = '0: Subscribtion ID not found'
ELSE
BEGIN
SELECT TOP 1 @EndDate = [enddate],
@MonthCounts = [subscriptionmonthcount]
FROM [Norton].[dbo].[subscriptionstatus]
WHERE [reference] = @Reference
AND [subscribtionid] = @SubscribtionID
IF @EndDate = '0'
UPDATE [Norton].[dbo].[subscriptionstatus]
SET [subscriptionmonthcount] = @Months + @MonthCounts
WHERE [reference] = @Reference
AND [subscribtionid] = @SubscribtionID
ELSE
UPDATE [Norton].[dbo].[subscriptionstatus]
SET [subscriptionmonthcount] = @Months
WHERE [reference] = @Reference
AND [subscribtionid] = @SubscribtionID
SET @Result = '1: Done Successfully'
END
END
GO
Upvotes: 1
Reputation: 51514
Remove the go
from before the end of the procedure.
ie
END
SET @Result = '1: Done Successfully'
END
GO --- <-- get rid of this
END
Upvotes: 4