Problem solve
Get help with specific problems with your technologies, process and projects.
Passing column and table names as parameters
DTS/SSIS Joe Toscano answers a user's question about passing column and table names as parameters within a stored procedure.
Is it possible to pass the column names and table name as parameters in the select clause within a stored procedure? If so how can I accomplish this? (eg: Select @column1,@column2 .. from @table1).
Try looking into using dynamic SQL. The simplest would be to pass the table/column(s) as parameters and using the exec command to execute a Transact-SQL command you construct. Below is an example where I use local variables to construct a statement that retrieves data from a table in the Northwind database. You will notice that by using exec (SQL command), you can dynamically construct the SQL command. For complex commands I've used another local variable such as @sqlcmd varchar(2048) to hold the actual command. Below is a simple example:
declare @tablename varchar(64), @columnname varchar(64) select @tablename = 'Categories', @columnname = 'CategoryName'...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Step 2 of 2:
exec ('select ' + @columnname + ' from ' + @tablename)
You may consider using sp_executesql which accomplishes the same, but the Query Optimizer is more likely to re-use the execution plan if only the parameters change. Please see Books Online for some detailed examples. Be aware that using dynamic SQL while very flexible may not be very efficient in some cases. Look the query plans the optimizer generates and compare the exec statement and sp_executesql's performance.
Start the conversation
0 comments