By
Published: 20 Feb 2006
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.
Dig Deeper on SQL Server Stored Procedures
Migrate DTS packages to SQL Server Integration Services with Migration Wizard and deploy SSIS packages in SQL 2005. Learn how to access DTS Designer ...
Continue Reading
Can I call triggers in stored procedures or vice versa?
Continue Reading
I have a table, let's call it AlarmTriggers and it has a Boolean field pulled into it. When my software writes to it the table doesn't set the field ...
Continue Reading