question

Annaamuthu1990 avatar image
Annaamuthu1990 asked

Sql server conditional Join while using parameter

Hi Folks,

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.

Thanks

sql server 2012joinsstored proceduresjoin hints
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
WRBI avatar image
WRBI answered

Hi @Annaamuthu1990,

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?

Dynamic SQL

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);

IF Statement

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.

Regards,

WRBI

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.