question

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