Implement 'USE' + @DB inside procedure

Question

Implement 'USE' + @DB inside procedure

SQL_By_Chance

Hall of Fame

Points: 3915
More actions
October 12, 2011 at 9:39 am

#260272

Hi,
Does anyone know how to implement use of different database inside a proc ?
create proc xyz
as
begin
declare @sql varchar (100)
set @sql = 'use ' + @dbname /* This is not working */
sp_executesql @sql
Please suggest some turn around other than adding database calling.
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

SQL_By_Chance (10/12/2011)
Hi,
Does anyone know how to implement use of different database inside a proc ?
create proc xyz
as
begin
declare @sql varchar (100)
set @sql = 'use ' + @dbname /* This is not working */
sp_executesql @sql
Please suggest some turn around other than adding database calling.

To accomplish what you are attempting, you need to use dynamic sql for the query inside your stored procedure as well. You would prepend the dynamic sql statement with the use statement.

What you attempted actually changed the database during the execute of @sql but upon return you were back to the original database.

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

You don't need the USE statement:

CREATE PROC xyz

AS

BEGIN

DECLARE @sql NVARCHAR (100) = 'select db_name()'

DECLARE @dbname VARCHAR (100) = 'tempdb'

DECLARE @finalsql NVARCHAR(MAX) = @dbname + N'.dbo.sp_executesql @sql'

EXEC sp_executesql @finalsql,N'@sql nvarchar(100)', @sql

END

-- Gianluca Sartori

SQL_By_Chance Hall of Fame Points: 3915 More actions · Answer 3

Thanks Lynn,

This question is directed to you

Did you mean something like this ?

@sql = ' use ' + @dbname + 'GO' + ' Insert/Update/Delete ..'

______________________________________________________________________

Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”

SQL_By_Chance Hall of Fame Points: 3915 More actions · Answer 4

Thanks Gianluca,

Does this mean : [tempdb.sp_executsql @sql ] would get executed in Temp DB ?

______________________________________________________________________

Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

SQL_By_Chance (10/12/2011)
Thanks Gianluca,
Does this mean : [tempdb.sp_executsql @sql ] would get executed in Temp DB ?

Yep.

-- Gianluca Sartori

Adam Kreul Ten Centuries Points: 1391 More actions · Answer 6

Using dynamic sql is the way to go. Here's an example code snippet:

SELECT @sqlCMD = 'USE [' + @databaseNm + '];

INSERT INTO #tmpOrphanUsers (dropCmdTxt, Completed)

SELECT '+''''+ 'USE [' + @databaseNm + ']; DROP USER [' + '''' + ' + u.name + ' + '''' + ']' +''''+ ' AS dropCmdTxt, 0 AS Completed

FROM master..syslogins l

RIGHT JOIN sysusers u ON l.sid = u.sid

WHERE l.sid IS NULL

AND issqlrole <> 1

AND isapprole <> 1

AND ( u.name <> ' + '''' + 'INFORMATION_SCHEMA' + ''''

+ ' AND u.name <> ' + '''' + 'guest' + ''''

+ ' AND u.name <> ' + '''' + 'dbo' + ''''

+ ' AND u.name <> ' + '''' + 'sys' + ''''

+ ' AND u.name <> ' + '''' + 'system_function_schema' + '''' + ')'

EXEC sys.sp_executesql @sqlCMD

This is from a stored procedure where I'm passing in the value of @databaseNm when the proc is called.

Gianluca's solution would work just as well.

SQL_By_Chance Hall of Fame Points: 3915 More actions · Answer 7

Gianluca's solution would work just as well.

Thanks CheeseHead.Completely Agree. 🙂

______________________________________________________________________

Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”

Jared SSC Guru Points: 61793 More actions · Answer 8

CheeseheadDBA (10/12/2011)
Using dynamic sql is the way to go. Here's an example code snippet:
SELECT @sqlCMD = 'USE [' + @databaseNm + '];
INSERT INTO #tmpOrphanUsers (dropCmdTxt, Completed)
SELECT '+''''+ 'USE [' + @databaseNm + ']; DROP USER [' + '''' + ' + u.name + ' + '''' + ']' +''''+ ' AS dropCmdTxt, 0 AS Completed
FROM master..syslogins l
RIGHT JOIN sysusers u ON l.sid = u.sid
WHERE l.sid IS NULL
AND issqlrole <> 1
AND isapprole <> 1
AND ( u.name <> ' + '''' + 'INFORMATION_SCHEMA' + ''''
+ ' AND u.name <> ' + '''' + 'guest' + ''''
+ ' AND u.name <> ' + '''' + 'dbo' + ''''
+ ' AND u.name <> ' + '''' + 'sys' + ''''
+ ' AND u.name <> ' + '''' + 'system_function_schema' + '''' + ')'
EXEC sys.sp_executesql @sqlCMD
This is from a stored procedure where I'm passing in the value of @databaseNm when the proc is called.
Gianluca's solution would work just as well.

I completely disagree. Using dynamic SQL when not necessary can be dangerous and can also be a performance hit. In this case, it may not be either of those, but it is a pain and a bad way to script. I suggest the above solution that qualifies the database name with the table.

Thanks,

Jared

Jared
CE - Microsoft

Adam Kreul Ten Centuries Points: 1391 More actions · Answer 9

I was never suggesting to use dynamic sql when not necessary. Of course if it isn't necessary, you wouldn't use it. The code I posted above was from a process where it was necessary in that particular case. I was never suggesting to use dynamic sql everytime, only responding to the original question being asked.

spaghettidba SSC Guru Points: 105732 More actions · Answer 10

I know it's not exactly what you asked for, but I guess you are trying to do something that this stored procedure[/url] could solve quite easily.

-- Gianluca Sartori