February 27, 2008 at 3:18 pm
Hey everyone,
I'm getting a rather bizarre (but probably simple) error when trying to check for the existence of a procedure before creating it. I know I could just drop it, but I'm really curious as to why this process doesn't work right.
IF OBJECT_ID(N'dbo.spCoverageLog') IS NULL
CREATE PROCEDURE [dbo].[spCoverageLog] ( @spName VARCHAR(100) )
AS
BEGIN
UPDATE dbo.CodeCoverage
SET CreationDate = GETDATE(),ProcedureName = @spName;
END
GO
It's generating a syntax error at PROCEDURE.
Anyone know why this might happen?
The solution was to run the CREATE PROCEDURE statement with EXEC, but it shouldn't be.
February 27, 2008 at 3:36 pm
Remove the first line in the code that hsould work. Else add a drop procedure statement and a go after that for the code to work fine.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 27, 2008 at 3:40 pm
Here is the working code.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCoverageLog]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spCoverageLog]
GO
CREATE PROCEDURE [dbo].[spCoverageLog] ( @spName VARCHAR(100) )
AS
BEGIN
UPDATE dbo.CodeCoverage
SET CreationDate = GETDATE(),ProcedureName = @spName;
END
GO
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 27, 2008 at 3:41 pm
Hey Sugesh,
Thanks for the reply.
I know dropping the procedure first works just fine. My essential question is this:
How do you create a stored procedure only if it doesn't already exist?
I would have thought it would be exactly the same as a table, but it doesn't work for the reason I indicated.
Thanks!
Nate
February 27, 2008 at 4:33 pm
For the record, according to BOL, there's just no way to directly combine CREATE PROCEDURE with any other T-SQL statement within a batch.
February 27, 2008 at 4:39 pm
Take a page out of the playbook used in the Generate SCripts functionality. In order to do that - put the create procedure script into a sp_execSQL command (make the create a dynamic SQL statement).
That way you can do
If not exists(....)
Exec Sp_execsql 'Create proc....'
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 27, 2008 at 7:55 pm
As stated in BOL:
"The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch."
You need to do something like this:
[font="Courier New"]IF OBJECT_ID ( N'dbo.spCoverageLog' ) IS NOT NULL
DROP PROCEDURE dbo.spCoverageLog;
GO
CREATE PROCEDURE.....
GO
[/font]
Note - you don't need the BEGIN...END in the CREATE PROCEDURE statement.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 27, 2008 at 10:34 pm
... which is why you encapsulate it within a sp_execSQL call. The script within Sp_execsql runs within its own scope, and therefore meets the "within its own batch" rule.
Want a sample? Right-click on a database, pick Tasks, Generate Scripts. turn the "Include IF NOT EXISTS" option on, and pick something. Look at what it does and emulate...
Voila as they say across the pond.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 28, 2008 at 9:54 am
Right on guys; thanks very much for the responses.
That was essentially the solution I used (running the CREATE PROCEDURE statement with EXEC).
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply