Friday, January 10, 2014

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

Problem
I am installing stored procs with a service and when I load and execute the query strings I get the error in the title. The query string is in format below...

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StoredProcName]') AND type in (N'P'))
DROP PROC dbo.StoredProcName
GO

CREATE PROCEDURE [dbo].StoredProcName
...
...
GO
grant execute on dbo.StoredProcName to SECURITYGROUPNAME

Solution
Wrap the stored proc in a exec('') statement. So it would need to be modified as such:


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StoredProcName]') AND type in (N'P'))
DROP PROC dbo.StoredProcName
BEGIN
exec('

CREATE PROCEDURE [dbo].StoredProcName
...
...
')
exec('
grant execute on dbo.StoredProcName to SECURITYGROUPNAME
')
END

NOTE:
Make sure to escape single-quote marks, or the exec will fail.
For example,
exec('SELECT * FROM TableName WHERE Name = 'Test'')
This would fail, you need to change that to this:
exec('SELECT * FROM TableName WHERE Name = ''Test''')

Reference
http://social.msdn.microsoft.com/Forums/en-US/7328c11a-799a-42fa-883d-c68f9d7e6322/create-procedure-in-sql-server-problem

No comments:

Post a Comment

There was an error in this gadget