CODEWITHSUNDEEP
sqlsql-serverstored-procedures
jacksonSD
jacksonSD

Reputation: 677

Stored Procedure, 'incorrect syntax error'

Attempting to figure out stored procedures, and I'm getting this error:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Procedure'.

The error seems to be on the if, but I can drop other existing tables with stored procedures the exact same way so I'm not clear on why this isn't working. Can anyone shed some light?

Begin
Set nocount on 
Begin Try

    Create Procedure uspRecycle
    as
        if OBJECT_ID('Recycle') is not null
            Drop Table Recycle

        create table Recycle
            (RecycleID integer
                constraint PK_integer primary key,
            RecycleType nchar(10) not null,
            RecycleDescription nvarchar(100) null)

            insert into Recycle
                (RecycleID,RecycleType,RecycleDescription)
            values ('1','Compost','Product is compostable, instructions included in packaging')
            insert into Recycle
                (RecycleID,RecycleType,RecycleDescription)
            values ('2','Return','Product is returnable to company for 100% reuse')
            insert into Recycle
                (RecycleID,RecycleType,RecycleDescription)
            values ('3','Scrap','Product is returnable and will be reclaimed and reprocessed')
            insert into Recycle
                (RecycleID,RecycleType,RecycleDescription)
            values ('4','None','Product is not recycleable')



End Try

Begin Catch
  DECLARE @ErrMsg nvarchar(4000);
  SELECT @ErrMsg = ERROR_MESSAGE();
  Throw 50001, @ErrMsg, 1;
End Catch

-- checking to see if table exists and is loaded:
If (Select count(*) from Recycle) >1 
    begin
        Print 'Recycle table created and loaded '; Print getdate()
    End
set nocount off 
End

Upvotes: 0

Views: 694

Answers (2)

PrfctByDsgn
PrfctByDsgn

Reputation: 1050

you could also just put an exec ('...') around your create procedure statement ... like so:

exec('Create Procedure uspRecycle
as
    if OBJECT_ID(''Recycle'') is not null
        Drop Table Recycle

    create table Recycle
        (RecycleID integer
            constraint PK_integer primary key,
        RecycleType nchar(10) not null,
        RecycleDescription nvarchar(100) null)

        insert into Recycle
            (RecycleID,RecycleType,RecycleDescription)
        values (''1'',''Compost'',''Product is compostable, instructions included in packaging'')
        insert into Recycle
            (RecycleID,RecycleType,RecycleDescription)
        values (''2'',''Return'',''Product is returnable to company for 100% reuse'')
        insert into Recycle
            (RecycleID,RecycleType,RecycleDescription)
        values (''3'',''Scrap'',''Product is returnable and will be reclaimed and reprocessed'')
        insert into Recycle
            (RecycleID,RecycleType,RecycleDescription)
        values (''4'',''None'',''Product is not recycleable'')')

Upvotes: 0

Anup Shah
Anup Shah

Reputation: 1254

Create procedure stmt should be first in batch. You can not do that inside try catch block. Unless you use dynamic sql to create a procedure

Upvotes: 1

Related Questions