Thursday, 7 March 2013

MS SQL - How to fix error Incorrect syntax near the keyword 'PROCEDURE'. Expecting External.

I am getting this error when trying to alter stored procedure.  Before that I am checking if this stored procedure exists.

Watch this example on youtube



if Exists(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TestSubQuery') AND type in (N'P', N'PC'))
Begin
Alter Procedure [dbo].[TestSubQuery]
Go

CREATE PROCEDURE [dbo].[TestSubQuery]
    @StyleName As VarChar(50)
AS
BEGIN
    SET NOCOUNT ON;
   
    Declare @GroupId int
   

    Select @GroupId = GroupId From StyleMaster Where(StyleName = @StyleName)
   
    If @GroupId = 1
    Begin
        Select GroupId, GroupName, GroupDescription
        From GroupMaster
        Where GroupId = @GroupId
    End
    Else
    Begin
        Select StyleId, StyleName, StyleDescription
        From StyleMaster
        Where GroupId = @GroupId
    End
END
End

To fix it - 
1. Check if stored procedure exists.
2. If yes - drop it
3. Add modified stored procedure

if Exists(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TestSubQuery') AND type in (N'P', N'PC'))
Drop Procedure [dbo].[TestSubQuery]
Go

CREATE PROCEDURE [dbo].[TestSubQuery]
    @StyleName As VarChar(50)
AS
BEGIN
    SET NOCOUNT ON;
   
    Declare @GroupId int
   

    Select @GroupId = GroupId From StyleMaster Where(StyleName = @StyleName)
   
    If @GroupId = 1
    Begin
        Select GroupId, GroupName, GroupDescription
        From GroupMaster
        Where GroupId = @GroupId
    End
    Else
    Begin
        Select StyleId, StyleName, StyleDescription
        From StyleMaster
        Where GroupId = @GroupId
    End
END

1 comment:

  1. You made some decent factors there. I looked on the internet for the problem and found most individuals will go along with together with your website. online casino slots

    ReplyDelete