Stored Proc creation script in T-Sql

Transact-SQL

endemol


I am trying to do the following: I need to execute a t-sql statement that contains the stored procedure creation script.
In reality this will have differnt stored procedure creation scripts which will be created in different databases depending upon certain criteria.
The example below is just for demo purposes. when i try to execute this in master it is showing me the following errors:

USE MASTER

GO

declare @SQLString Nvarchar(1000)

declare @STR Nvarchar(1000)

declare @DBName nvarchar(100)

SET @DBName ='BTGenesisMDRockville'

SET @STR='

create proc sptemp

@id int

as

select * from orders where id=@id

go

'

set @SQLString='USE ' + @DBName + char(10) +'GO'

set @SQLString =@SQLString + @str

EXEC (@SQLString)

Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near 'GO'.

Msg 111, Level 15, State 1, Line 3

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

Msg 137, Level 15, State 2, Line 6

Must declare the variable '@id'.

Can anyone help me on this



Top

Re: Stored Proc creation script in T-Sql

Tom Phillips


You can't set @str with multiple lines that way. Try this:

SET @STR='create proc sptemp ' + CHR(10) +

'@id int ' + CHR(10) +

'as ' + CHR(10) +

'select * from orders where id=@id ' + CHAR(10) +

'go'



Top

Re: Stored Proc creation script in T-Sql

endemol

Giving similar errors:

Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near 'GO'.

Msg 111, Level 15, State 1, Line 2

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

Msg 137, Level 15, State 2, Line 5

Must declare the variable '@id'.



Top

Re: Stored Proc creation script in T-Sql

richbrownesq

OJ has come up with the something similar in the following thread. Brilliant.

http://forums.microsoft.com/TechNet/ShowPost.aspx PostID=2218957&SiteID=17

HTH!





Top

Re: Stored Proc creation script in T-Sql

endemol

Thanks.. That was helpful..

Can i also check whether the proc already exists before creating the script something like this:

set @sp=@logDBName + '..sp_executesql'

SET @STR='

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS OFF

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[sptemp]'') AND OBJECTPROPERTY(id, N''IsProcedure'') = 1)

DROP PROCEDURE [dbo].[sptemp]

CREATE PROC sptemp

@id int

as

select * from orders where id=@id

go

'

EXEC @sp @STR


Top

Re: Stored Proc creation script in T-Sql

endemol

I think i found out the answer.

Thanks richbrown. Your link was very helpful!!!!


Top