General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Sergey,
As always, thanks for the timely advice.
Regards,
Jim
>James,
>
>From BOL: "The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch". The same is true for ALTER PROCEDURE.
>
>>I am trying to check for the existance of a procedure and if it exists I want to alter it. If not I want to create it. When I place this code in the Query Analyzer of MSFT I get an error saying there is a problem near Procedure in line 2. Does anyone know why?
>>
>>If I delete all lines except for the IF and add in a DROP PROCEDURE prGetEqId it works. If I just put in the Alter Procedure it works.
>>
>>If I put in a BEGIN/END after the IF I get an error.
>>
>>
>>Thanks for any assistance in advance.
>>
>>Jim
>>
>>HERE IS THE CODE:
>>
>>IF exists(select * from sysobjects where id = object_id('prGetEqId') and OBJECTPROPERTY(id,'IsProcedure')=1)
>>Alter Procedure prGetEqId
>> @Make varchar(50),
>> @Model varchar(50)
>> as
>> SELECT EquipmentId
>> FROM Equipment
>> WHERE Make = @Make
>> and Model = @Model
>>ELSE
>> Create Procedure prGetEqId
>> @Make varchar(50),
>> @Model varchar(50)
>> as
>> SELECT EquipmentId
>> FROM Equipment
>> WHERE Make = @Make
>> and Model = @Model
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only