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
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With