I am creating a stored procedure which takes a few paramaters. One of which is a string of comma separated codes. At the end of my select query the codes will be used in the where clause.
What I'm doing is parsing the comma separated string and populating a temp table called #codes. So my query will look something like this:
select * from tableA
where tableA.col1 = 'something'
and tableA.code in (select * from #codes)
However, the code paramater can be null and if this is the case I want the query to be called for all codes - i.e. I effectively want to remove the and tableA.code in (select * from #codes) part of the where clause.
Is there any clever way of doin this other than having a if... else... and writing 2 queries - one with the and and one without it?
Thanks for the response, but I'm not sure if I understand correctly. The parameter will be passed in so will look something like this:
@param1 = 'codeA,codeB,codeC,codeD'
I will then take the string and create a temp table (#codes) which in this case will have 4 rows. If I just do where tableA.code IN (@param1) no results are returned but if I do where tableA.code IN (select * from #codes) I get results.
So, param1 can be null and if it is then I want the results for all codes returned - so there might also be codeE, codeF, codeG so the query would return answers for codeA - codeG.
If I leave the clause where tableA.code (select * from #codes) in the query, #codes will be an empty table and therefore no rows will be returned? I need a way of eliminating this clause when #codes has no rows.