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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Where clase - column name as parameter

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
Go to Top of Page

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
...
Go to Top of Page

tech_1
Posting Yak Master

129 Posts

Posted - 2014-09-20 : 09:39:58
gbritton - can you provide a fuller example please?
Go to Top of Page

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
= @columnValue



One thing remains - what if both values are not supplied - how can I only add the WHERE clause if the parameter values are supplied?
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -