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 |
|
tech_1
Posting Yak Master
129 Posts |
 Posted - 2014-09-20 : 04:27:28
|
| Question:in a SPROC I am creating, is there a way to use a columnName as a parameter and then do a filter on that based on a second parameter such as @columnValue ?So instead of having to construct the WHERE clause or doing a bunch of IF statements to see what the column name is from the parameter and doing a query based upon that, is there a way to tell it to do a WHERE clause where @columnName = @columnValue ?I do not want to use dynamic SQL string concatenation... |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-09-20 : 07:00:06
|
| You will need to use dynamic SQL for that. No other option really |
 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-09-20 : 07:04:44
|
| Well one other option. A bid case stmt. Et.Case @colname When 'col1' and col1/= @colvalue... |
|
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2014-09-20 : 09:39:58
|
| gbritton - can you provide a fuller example please? |
|
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2014-09-20 : 10:13:43
|
| This seems to work:SELECT ...... FROM Tracks p WHERE CASE @columnName WHEN 'TrackId' THEN CAST(p.TrackID as nvarchar(100)) WHEN 'TrackName' THEN p.TrackName WHEN 'FullDuration' THEN CAST(p.FullDuration as nvarchar(100)) WHEN 'ID' THEN CAST(p.TrackID as nvarchar(100)) END = @columnValueOne thing remains - what if both values are not supplied - how can I only add the WHERE clause if the parameter values are supplied? |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-09-20 : 11:45:56
|
| Give em default values or add where var is null or....case |
|
|
|
|
|
|
Where clase - column name as parameter