I have a parameter @status.It has values 0,1,2,3.When the value is 0 i want to make a left join and while the value is 1,2 or 3 , i want to make a inner join with my transaction table.
Answer by WRBI ·
I can only think of the following ways of doing this at the moment. Neither of these have been tested for performance etc.
Also, is there no other option rather that messing around with joins based on parameters. To me something doesn't sit right about it. You could just have two separate SPROCS and call them based on whatever is setting the parameters 0,1,2,3,4?
Be careful of SQL injection, you can test whats being out into parameters etc.
DECLARE @YourParameter TINYINT = 0; DECLARE @SQL NVARCHAR(MAX); SELECT @SQL = 'SELECT TOP 10 c.ID FROM Posts as p ' + CASE WHEN @YourParameter = 1 THEN 'LEFT JOIN Comments as c on c.PostId = p.ID' ELSE 'INNER JOIN Comments as c on c.PostId = p.ID' END; -- Use print to debug dynamic sql. -- PRINT @SQL; EXEC sp_executesql(@SQL);
DECLARE @YourParameter TINYINT = 0; IF (@YourParameter = 0) BEGIN -- Do your left join query in here. END ELSE BEGIN -- Do your inner join query in here. END;
There might be other options that someone else can suggest.