1

I have a stored procedure I want to pass a parameter to. I want to adjust my Select statement based on whether the parameter is null or contains a value. How do I structure the Select case to have a codeblock rather than a simple assignment after the "Then" or "Else"

        Select Case isnull(@variable,'null') 
               when 'null' then select x from table 
               else select x from table where table.field = @variable

I tried putting into Begin and end but that does not seem to work

Note: This is not a duplicate of the question about using parameters in a stored procedure- it is about a code block in a "case.. when" statement. The usage of the parameter is part of the example, not part of the question

Marc L
  • 837
  • 9
  • 19
  • 40
  • Possible duplicate of [How can I use optional parameters in a T-SQL stored procedure?](https://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure) – EzLo Jun 12 '18 at 08:54
  • Catch-all queries are *bad*. What you tried to write is equivalent to `select x from table where table.field = @variable or @variable is null` and it's still bad. You don't need such queries when you use ORMs like EF or NHibernate and LINQ. – Panagiotis Kanavos Jun 12 '18 at 08:58
  • What is the *actual* problem you want to solve by using such a catch-all query? – Panagiotis Kanavos Jun 12 '18 at 08:58
  • Sending a null parameter to a report query. If the parameter is null then exclude the requirement from the where clause otherwise include it – Marc L Jun 12 '18 at 09:04

1 Answers1

1

I believe you want IF-ELSE:

IF @variable IS NULL 
   select x from table;
ELSE
   select x from table where table.field = @variable;

Or 

select x from table 
where (table.field = @variable OR @variable IS NULL)
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 1
    Second solution even better and much neater! Thanks – Marc L Jun 12 '18 at 09:23
  • @MarcL Then please accept the response as the correct answer. That is how SO works - questions are **answered** so others can find solutions. – SMor Jun 12 '18 at 12:27