Yasser
Yasser

Reputation: 117

Error when creating stored procedure

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

Answers (2)

Gidil
Gidil

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

podiluska
podiluska

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

Related Questions