CODEWITHSUNDEEP
sql-servert-sql
ShaQue
ShaQue

Reputation: 366

Not allowing to execute the Stored procedure

I want to execute my sp using dynamic sql where iam passing the database name in a parameter

If I am using this way i have to use floowing code

use @dbName

go

exec(@Sql)

where @dbName is reffered for the datbase name and @Sql reffers an entire queries which i want to execute Iam using Sql server 2005 and my code is as follows

set @cSQL=@dbName+'.dbo.sp_helptext '''+@cName+''''

now i want to execute this stored procedure so target database is new database, it needs an use @dbname in next line Go followed by an sql statement

Can anyone help me in this?

Upvotes: 1

Views: 84

Answers (1)

David
David

Reputation: 34543

I just tried this and it works on SQL Server 2000 without a USE or a GO...

declare @dbname as varchar(100), @sql as varchar(100)
set @dbname = 'my-database'
set @sql = 'my-stored-procedure'

set @Sql = QUOTENAME(@dbName) + '.dbo.sp_helptext ''' + @Sql + ''''
exec(@Sql)

Upvotes: 1

Related Questions