How to run this command in stored procedure?

[icemel]

Hi,

I have a stored procedure that I want to be able to select the database it runs against.

For example, I want to pass the name of the database to the stored procedure, then have it run against the tables in THAT database.

SET @usedb = 'USE ' + @dbname << passed-in parameter

e.g.) USE MY_DATABASE - as you would run this from Query Analyzer, except I want to run this in the stored procedure itself.

Can this be done, and if so - how?

I can't use EXEC @usedb - treats it like an sp call

Thanks

 

[druer]

EXEC('use '+ @DNAME)

Will work for you. However, the key to consider is that the environment for the EXEC command will be using that database, however, as soon as it returns it will be back in your starting database.

The few modules I have that do that do the following:

EXEC('use '+ @DNAME + ' all of the other things I want done')

The other option is to modify the rest of your sql to dynamic sql so that it simply prepends the dbname before the tables:

set @command = "select a,b,c from table"
becomes
set @command = "select a,b,c from '+@DNAME+'.dbo.table'

etc.

 

[icemel]

Hi druer,

Do you have a code sample using dynamic SQL?

Thanks

 

[icemel]

Actually, what I need to do is this:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES << from the database I choose
WHERE TABLE_TYPE = 'BASE TABLE'

I want to set this to select from the database of my choice by means of a passed-in parameter.

Is this possible?

 

[druer]

EXEC('use '+ @DNAME + ' select table_name from information_schema.tables where table_type = ''BASE TABLE''')

Should do what you are looking to do. Notice that I've used 2 single quotes in a row to get the command to accept it as an actual single quote character instead of ending the command. And at the end there are 3 because the first two convert to a real single quote, while the third ends the command itself.

 

[thegeezza]

Morning,

Remeber, it is good practice to make use of the quotename() function to wrap your variables passed as parameters to avoid SQL injection to your code.

Regards,
TheGeezza