CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
chloe06060
chloe06060

Reputation: 3

combine two T-SQL in a dynamic way

How can I using something like if or case..when to combine the following code into one?

if @para = 'test'
begin
select * from Table A where status='A' and id in (select id from Table B)
end
else if @para = 'others'
begin
select * from Table A where status='A' and id in (select id from Table c)
end

like select * from Table A where id in if @para = XXX then (select id from Table B)

Thanks a lot.

Upvotes: 0

Views: 47

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this:

select * 
from Table A 
where (id in (select id from Table B) and @para = 'test')
      OR
      (id in (select id from Table c) and @para = 'others')

Upvotes: 1

Veljko89
Veljko89

Reputation: 1953

As you tagged stored procedure i made it as one ... try it like this 

create procedure GetData (@para nvarchar(100))
as 
begin

declare @sql nvarchar(max)

set @sql = case 
            when @para = 'test'
        then 
            'Select * from TableA A
                join TableB B on A.id = B.ID'
       else -- if @para = 'others' goes into else
            'Select * from TableA A
                join TableB B on A.id = B.ID'
        end

execute (@sql)
end

Upvotes: 1

Related Questions