USE execute to execute Stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

USE execute to execute Stored procedure

Author

Topic

kalwar
Starting Member

3 Posts

Posted - 2011-09-23 : 19:50:56

Hello all,
Here is my problem. I need to execute the sp_addrolemember procedure using the EXEC command. The reason for this is, I need to change the current DB context dynamically
So my code is as follow;
set @sql='Use '+@dbname+';'+ ' EXEC sp_addrolemember ''db_owner'','''+ @User +''';';
execute @sql ;[/

The resulting string is;

Use TestDb2; EXEC sp_addrolemember 'db_owner','TestUser2';

I can take this string and execute it in a Query window and works fine. But passing this string to Execute gives me the following error.

Could not find stored procedure 'Use TestDb2; EXEC sp_addrolemember 'db_owner','TestUser2';'.

Any one have any insight?

Regards.
Kumar Alwar.

KAA

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-23 : 21:59:13

[code]
Declare @dbname sysname
Declare @user sysname
set @dbname = 'YourDatabase'
set @user = 'guest'

EXEC ('use [' + @dbName + ']; exec sp_addrolemember @rolename = ''db_datareader'', @membername = ''' + @user + '''');[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources