Extending a stored procedure (adding a single part to it) by using a parameter in SQL Server

Question

I have a 200 line long stored procedure, which gets a parameter 'prmtr',

What I want to do is add an "sql part" to my stored procedure, according to my parameter.

example:

SELECT A.* FROM
(
SELECT * FROM table1
) A

IF (my parameter) = a

LEFT JOIN
(
SELECT * FROM table2
) B
ON A.ID= B.ID

Answer 1

Maybe this is useful for you: http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx

Answer 2

You cannot change a query like this - so you have two choices:

• have two separate queries in the SPROC (one for each branch) - OK for two, but doesn't scale very well to more complex combinations
• use dynamic SQL; i.e. build the query in a varchar(4000) and use sp_ExecuteSQL to invoke it; fairly obviously you can change a string to include an extra 'LEFT JOIN ...' etc.

All that said, though, I generally prefer a single SPROC to always return the same schema - it completely messes with most ORM tools if you change the columns based in the parameters. Something to watch...

Answer 3

You can do it like this

SELECT  A.* 
FROM    (
      SELECT * 
      FROM table1
     ) A LEFT JOIN 
     (
      SELECT * 
      FROM table2
     ) B ON @prmt = 'A'
      AND A.ID= B.ID