mssqlserver.com: FAQ: How can I issue a SQL command that uses a variable for the tablename, columns etc.

How can I issue a SQL command that uses a variable for the tablename, columns etc. How can I return values from some dynamic SQL?

Look up the EXEC command. Also sp_executesql. Note that the user must have permissions to the underlying tables/views etc. when running dynamic SQL like this - it does not inherit the permissions from a stored-procedure like the static SQL in an SP does.

A short example that selects a column from a table :-

USE pubs
go

DECLARE @str varchar(255)
DECLARE @columnname varchar(30)

SELECT @columnname='au_lname'

SELECT @str = 'SELECT ' + @columnname + ' FROM authors'
EXEC (@str)

-------------------------------

sp_executesql examples that return values.

declare @str nvarchar(500), @count int
set @str = N'select @count = count(*) from pubs..authors'
execute sp_executesql @str, N'@count int out', @count out
select @count

declare @str nvarchar(500), @au_lname nvarchar(500)
set @str = N'select TOP 1 @au_lname = au_lname from pubs..authors'
execute sp_executesql @str, N'@au_lname nvarchar(500) out', @au_lname out
select @au_lname

-------------------------------

An EXEC example from the books-online :-

This example shows how EXECUTE handles dynamically built strings with variables. This example creates a cursor (tables_cursor) to hold a list of all user-defined tables (type = 'U').
DECLARE tables_cursor CURSOR
    FOR
    SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @tablename varchar(30)
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
    /*
        A @@fetch_status of -2 means that the row has been deleted.
        No need to test for this as the result of this loop is to
        drop all user-defined tables.
    */
    EXEC ("DROP TABLE " @tablename)
    FETCH NEXT FROM tables_cursor INTO @tablename
END
PRINT "All user-defined tables have been dropped from the database."
DEALLOCATE tables_cursor

--------------

Michael R. Hotek

All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.