I'm trying to create a simple stored procedure which queries a sys.tables table.
CREATE PROCEDURE dbo.test
SET NOCOUNT ON
SET XACT_ABORT ON
SELECT TOP 100 *
WHERE name = @col
There are at least two ways to do this:
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;
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';
.<schema_name>. resolution operator allows you to query objects in a different database without using a
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