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

Question

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

CREATE PROCEDURE dbo.test
    @dbname NVARCHAR(255),
    @col NVARCHAR(255)
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON

    USE @dbname

    SELECT TOP 100 *
    FROM sys.tables 
    WHERE name = @col
GO

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 1

If you use EXEC @Var (without brackets - i.e. not EXEC (@Var)) SQL Server looks for a stored procedure matching the name passed in @Var. You can use three part naming for this.

If sys.sp_executesql is called with a three part name the context is set to the database in which it is called.

So you can do this with zero SQL injection risk as below.

CREATE PROCEDURE dbo.test @dbname SYSNAME,
                          @col    SYSNAME
AS
    SET NOCOUNT, XACT_ABORT ON;

    DECLARE @db_sp_executesql NVARCHAR(300) = QUOTENAME(@dbname) + '.sys.sp_executesql'

    EXEC @db_sp_executesql N'
                            SELECT TOP 100 *
                            FROM sys.columns 
                            WHERE name = @col',
                           N'@col sysname',
                           @col = @col 

Even if the above wasn't possible I'd still argue that it is perfectly possible to use dynamic SQL for this in a safe manner as here.

CREATE PROCEDURE dbo.test
    @dbname SYSNAME, /*Use Correct Datatypes for identifiers*/
    @col SYSNAME
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON

    IF DB_ID(@dbname) IS NULL  /*Validate the database name exists*/
       BEGIN
       RAISERROR('Invalid Database Name passed',16,1)
       RETURN
       END

DECLARE @dynsql nvarchar(max)  

 /*Use QUOTENAME to correctly escape any special characters*/
SET @dynsql = N'USE '+ QUOTENAME(@dbname) + N'

                         SELECT TOP 100 *
                         FROM sys.tables 
                         WHERE name = @col'

 /*Use sp_executesql to leave the WHERE clause parameterised*/
EXEC sp_executesql @dynsql, N'@col sysname', @col = @col

Answer 2

EDIT

My answer assumes some things which make this approach effectively useless. Unfortunately, SO will not let me delete the answer. I recommend @MartinSmith's answer (below in this thread). I think there's still some useful information here, BUT it doesn't actually solve the original problem. Godspeed.

Original Response

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.