Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TSQL: How to use variable that could be null or int in a single select

Tags:

sql

tsql

My question is can I do the functions of these two selects in a single statement and get rid of the IF?

DECLARE @recID INT;

--Case 1
SET @recID = null;
--Case 2
--SET @recID = 117;

IF @recID is null
BEGIN
    select * from myTable WHERE [myIDcolumn] is null    -- works when recID is null
END
ELSE
BEGIN
    select * from myTable WHERE [myIDcolumn] = @recID    -- works when recID is number
END
like image 825
Brad Boyce Avatar asked Apr 24 '12 13:04

Brad Boyce


People also ask

How do you assign a value to a variable in select query?

When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.

How do I use a variable in SQL Select statement?

The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.


1 Answers

How 'bout this:

select *
from myTable
WHERE [myIDcolumn] = @recID or (@recID is null and [myIDcolumn] is null)

If @recID is null, the first part will never be true but the second part will be if [myIDcolumn] is null, which covers the null case. If @recID is not null, the first part will match when appropriate (and the second part will be ignored). So both cases are covered.

like image 144
T.J. Crowder Avatar answered Sep 23 '22 10:09

T.J. Crowder