SQL Determine if parameter has a value

Question

How would I go about using an IF statement to determine if more than one parameter has a value then do some work, if only one parameter has a value then do other work. Is this possible with the SQL if? I am just trying to determine if one parameter has a value or if multiple parameters has value, because if so then I need to do something completely different. I have tried making a SQL statement but it didnt work properly. Could anyone point me in the right direction?

Solution 1

In SQL to check if a parameter has a value, you should compare the value to NULL. Here's a slightly over-engineered example:

Declare @Param1 int  
Declare @param2 int = 3

IF (@Param1 is null AND @param2 is null)
BEGIN
    PRINT 'Both params are null'
END
ELSE
BEGIN
    IF (@Param1 is null)
        PRINT 'param1 is null'
    ELSE 
    IF (@Param2 is null)
        PRINT 'param2 is null'
    ELSE
        PRINT 'Both params are set'
END

Solution 2

You can check the parameter values for NULL values. But it seems like to me at first blush you should check the parameters BEFORE going to the SQL...

Once you know what you need to do then call that SQL or Stored Procedure.

If you do it that way you can simplify your SQL because you only pass the parameters you really need to pass.

Actually the more I think of this the more important it is to do it this way. You can use the NULL values for other things that way.

Easy example: Getting a list of employees. Employees are Active or Inactive - Pass an @IsActive boolean parameter (if true only show active employees, if false show only inactive employees, if NULL then show all the employees)

Solution 3

In T-SQL you can use ISNULL function.

Solution 4

Here's an example

if @param1 = 'this value' and @param2 = 'another value'
  begin
  -- do things here
  end
else if @param1 = 'something else'
  begin
  -- do other work
  end
else
  -- do different things

View more solutions