Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stmaher
Starting Member
2 Posts |
 Posted - 2003-07-18 : 13:48:38
|
| In a stored procedure I'm going to pass in the name of a column to a variable. Then in my Select StatmentWHERE @variable = 12345. But MSSQL doesn't like this. What is the proper way to insert a variable as a column name into a select statement? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-18 : 13:57:54
|
| It's called dynamic SQL. Dynamic SQL should not be used unless all other methods have been tried already.DECLARE @SQL VARCHAR(7000)DECLARE @ColumnName SYSNAMESET @ColumnName = 'MyColumn'SELECT @SQL = 'INSERT INTO TableA (ColumnA, ColumnB)'SELECT @SQL = @SQL + ' SELECT Column1, Column2'SELECT @SQL = @SQL + ' FROM Table1'SELECT @SQL = @SQL + ' WHERE ' + @ColumnName + ' = 12345'EXEC (@SQL)But why do you need to do this? If you could post your stored procedure, we might be able to help you not use dynamic sql. Dynamic sql can not be pre-compiled, so you lose the benefits of a stored procedure. Because it can't be pre-compiled, it will run slower than if you were to write the explicit INSERT statement.TaraEdited by - tduggan on 07/18/2003 13:59:22 |
 |
|
|
stmaher
Starting Member
2 Posts |
Posted - 2003-07-21 : 10:07:18
|
| Yeah I know it's a horrible way to do things. I'm sure there is a better way to do it. But can it be done? |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-07-21 : 10:22:12
|
quote:
Yeah I know it's a horrible way to do things. I'm sure there is a better way to do it. But can it be done?
The other post shows how to do it, but because the folk here have mostly been there and done it, it also asks why you want to do it that way. If you post your procedure or the details of what you are trying to achieve, someone may be able to show you a better way of doing whatever it is you want to do.-------Moo. :) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-21 : 10:31:07
|
Umm... yes....Tara already gave you the answer...quote:
DECLARE @SQL VARCHAR(7000)DECLARE @ColumnName SYSNAMESET @ColumnName = 'MyColumn'SELECT @SQL = @SQL + ' SELECT Column1, Column2'SELECT @SQL = @SQL + ' FROM Table1'SELECT @SQL = @SQL + ' WHERE ' + @ColumnName + ' = 12345'EXEC (@SQL)
When he said INSERT though...I don't think he meant INSERT...More likely SET...Brett8-) |
|
|
|
|
|
|
Dynamic Columns