Q
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'...

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.

This was last published in February 2006

Dig Deeper on SQL Server Stored Procedures

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close