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