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

A. A. 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

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
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)
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
PRINT "All user-defined tables have been dropped from the database."
DEALLOCATE tables_cursor


Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.