CODEWITHSUNDEEP
sql-serverselectmultiple-columns
Teoman shipahi
Teoman shipahi

Reputation: 23042

Selecting same column list for different where conditions

I have a stored procedure returning same columns according to different parameters.

for example;

if name is not null
select a,b,c,d,e,f
from table1, table2, table3
where .....

if age is not null
select a,b,c,d,e,f
from table1, table2, table3,table4
where .....

if city is not null
select a,b,c,d,e,f
from table1,table3,table5
where .....

The problem is when I want to add/omit columns I will need to do it for every select.

Is there any way to keep column list once and use it for different where conditions?

Upvotes: 1

Views: 537

Answers (2)

bvr
bvr

Reputation: 4826

Try this

SELECT -- select list here
.....
WHERE
   name= CASE WHEN @name IS NULL THEN name ELSE @name END  
   AND age= CASE WHEN @age IS NULL THEN age ELSE @age END  
   AND city= CASE WHEN @city IS NULL THEN city ELSE @city END

Upvotes: 0

jazzytomato
jazzytomato

Reputation: 7214

You can use dynamic SQL

DECLARE @sql NVARCHAR(4000)
SET @sql = '
select a,b,c,d,e,f
from table1,table3,table5
where 1=1 '

IF @name IS NOT NULL
  SET @sql = @sql + ' AND name = ' + @city
IF @age IS NOT NULL
  SET @sql = @sql + ' AND age = ' + @age
IF @city IS NOT NULL
  SET @sql = @sql + ' AND city = ' + @city


EXEC sp_executesql @sql

Upvotes: 2

Related Questions