jjoras jjoras - 1 year ago 82
SQL Question

SQL Server: how to get a database name as a parameter in a stored procedure

I'm trying to create a simple stored procedure which queries a sys.tables table.

@dbname NVARCHAR(255),
@col NVARCHAR(255)

USE @dbname

FROM sys.tables
WHERE name = @col

This does not seem to work cause I should put GO after USE @dbname but this terminates the creation of this procedure? How can I put this database selction into this procedure so that a user can give a database name as a parameter for this proc?

Answer Source

There are at least two ways to do this:

  1. Use a case/switch statement (or ,in my example, a naive if..else block) to compare the parameter against a list of databases, and execute a using statement based on that. This has the advantage of limiting the databases that the proc can access to a known set, rather than allowing access anything and everything that the user account has rights to.

    declare @dbname nvarchar(255);    
    set @dbname = 'db1';    
    if @dbname = 'db1'
     use db1;
    else if @dbname = 'db2'
     use db2;
  2. Dynamic SQL. I HATE dynamic SQL. It's a huge security hole and almost never necessary. (to put this in perspective: In 17 years of professional development, I have never had to deploy a production system which used dynamic SQL). If you decide to go this route, limit the code that is dynamically called/created to a using statement, and a call to another stored proc do do the actual work. You can't just dynamically execute the using statement by itself due to scope rules.

    declare @sql nvarchar(255);
    set @sql = 'using '+@dbname+'; exec mydatabase..do_work_proc;';

of course, in your example, you could just do

    set @sql='select * from '+@dbname+'.sys.tables';

the .<schema_name>. resolution operator allows you to query objects in a different database without using a use statement.

There are some very, very rare circumstances in which it may be desirable to allow a sproc to use an arbitrary database. In my opinion, the only acceptable use is a code generator, or some sort of database analysis tool which cannot know the required information ahead of time.

Update Turns out you can't use in a stored procedure, leaving dynamic SQL as the only obvious method. Still, I'd consider using

select top 100 * from db_name.dbo.table_name

rather than a use.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download