Error when creating stored procedure

Question

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,

podiluska · Accepted Answer · 2012-10-17 12:43:36Z

4

Remove the go from before the end of the procedure.

ie

       END 

    SET @Result = '1: Done Successfully' 
    END 
GO  --- <-- get rid of this

END

answered Oct 17, 2012 at 12:43

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Gibson Over a year ago

Indeed. @Yasser, it's worth noting that GO is not actually a SQL statement. It's used to separate SQL statements, and is interpreted by the client tools, not the server. Basically, when you run the page of SQL there in Management Studio, etc., the client breaks the SQL into batches at the GO statements, and sends each batch separately to the server to be run, one after the other. Therefore, having a GO anywhere in between a BEGIN and END pair of a SQL statement will cause you a problem.

Yasser Over a year ago

Thanks podiluska for your answer, it fix the error but I got another one, near the first ELSE, fortunately, the code in Gidil answer worked. Thank you Matt for the information.

Gidil · Accepted Answer · 2012-10-17 12:47:41Z

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

Collectives™ on Stack Overflow

Error when creating stored procedure

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related