CODEWITHSUNDEEP
t-sql
ruedi
ruedi

Reputation: 5545

Assign range to variable using between

I have the following decitiontree:

declare @placeholder varchar(20)

If @Number1 = 1
AND @Number2 = 0
BEGIN SET @placeholder = 'NULL'
END
ELSE IF @Number1 = 1
AND @Number2 > 0
BEGIN SET @placeholder = Between (@Number2*10) AND (@Number2*10+9)
END 
ELSE
BEGIN
SET @placeholder = @Othervariable
END

I need the Variable for the query:

SELECT * FROM Table
WHERE @Placeholder is null or ID = @placeholder.

But the 'Between' part is not working. Can anyone help me with it?

Upvotes: 0

Views: 46

Answers (1)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

This won't work in SQL server - there is no variable type that holds something like lambda expression, say Between (@Number2*10) AND (@Number2*10+9).

One way is to store this in string (say, nvarchar(max)) and execute using exec() or sp_executesql().

The other (usually more optimized) way is to form the main expression to include sub-expressions along with exclusion criteria.

Here is an example:

DECLARE @Number1 int = 1 -- input variable 1
DECLARE @Number2 int = 1 -- input variable 2
DECLARE @excl1 bit = 0 -- exclusion criteria 1 (ec1)
DECLARE @excl2 bit = 0 -- exclusion criteria 2 (ec2)

-- fill excl. crit.
SET @excl1 = CASE WHEN @Number1 = 1 AND @Number2 = 0 THEN 1 ELSE 0 END
SET @excl2 = CASE WHEN @Number1 = 1 AND @Number2 > 0 THEN 1 ELSE 0 END

-- just output to see what's happening
PRINT @Number1
PRINT @Number2
PRINT @excl1
PRINT @excl2

SELECT * 
FROM Table
WHERE 
  -- if ec1 is active, we apply sub-expression 1
  (@excl1 = 0 OR 
    (@excl1 = 1 AND ID IS NULL))
  AND 
  -- if ec2 is active, we apply sub-expression 2
  (@excl2 = 0 OR 
    (@excl2 = 1 AND ID BETWEEN @Number2 * 10 AND @Number2 * 10 + 9))

Upvotes: 1

Related Questions