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

The only way to do this is to use Dynamic SQL, which is powerful but dangerous.

Read this article first.

Answer 2

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.

Answer 3

I'd argue that it is perfectly possible to use dynamic SQL for this in a safe manner as below.

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