T-SQL - parameter assignment, data retrieval and comparison in one line - is it possible?

Question

I want to do something like this in T-SQL, but it will return an error:

DECLARE @Stock int

IF(SELECT @Stock = [Stock] FROM dbo.Products WHERE [ProductID] = 1) > 5
    PRINT 'Stock is good: Current stock is ' + @Stock
ELSE
    PRINT 'Order some more - we are below minimum stock'

The error returned is:

Msg 102, Level 15, State 1, Line 2: Incorrect syntax near '='.

I can of course do something like this instead:

DECLARE @Stock int

SELECT @Stock = [Stock] FROM dbo.Products WHERE [ProductID] = 1
IF(@Stock > 5)
    PRINT 'Stock is good: Current stock is ' + @Stock
ELSE
    PRINT 'Order some more - we are below minimum stock'

I am not sure if it is just the way the T-SQL language works and there is nothing to do about it. I am basically looking for a shortcut to write less code :)

Anybody has some good ideas?

My suggestion: *if possible*, avoid T-SQL except for absolute model constraints :-) — , Jan 18 '11 at 04:23

score 2 · Answer 1 · edited May 23 '17 at 11:45

2

A MAX aggregate will return one row without a GROUP BY (as per my answer here)

SELECT
     CASE
         WHEN MAX(Z.Stock) > 5 THEN 'Stock is good: Current Stock' + CAST(MAX(Z.Stock) as varchar(10))
         ELSE 'Order some more - we are below minimum stock'
     END
FROM
    dbo.Products Z WHERE [ProductID] = 1

Edit:

You would never PRINT from a stored proc anyway..

--Do stuff

SELECT
     @Stock = MAX(Z.Stock),
     @UserMessage = 
         CASE
             WHEN MAX(Z.Stock) > 5
                 THEN 'Stock is good: Current Stock' + CAST(MAX(Z.Stock) as varchar(10))
             ELSE 'Order some more - we are below minimum stock'
         END
FROM
    dbo.Products Z WHERE [ProductID] = 1

--Do more stuff with @Stock

edited May 23 '17 at 11:45

Community

1
1

answered Jan 18 '11 at 06:00

gbn

422,506
82
585
676

Hi gbn - thanks for the answer, but it was not quite what I was looking for. I want to reuse the @Stock parameter later in the proc. – Sha Jan 18 '11 at 16:12
+1 That's actually quite clever. If there are no products where ProductId = 1, you still get a row. – Thomas Jan 18 '11 at 16:53
PRINT was just for illustrative purposes :-) I will use CASE/WHEN instead of using IF(assignement) similar to the way you have written. – Sha Jan 18 '11 at 17:23

Thomas · Accepted Answer · 2011-01-18T16:50:07.470

1

Assuming you are not planning on using @Stock later in the procedure you can do:

Select Case
        When Z.Stock > 5 
            Then 'Stock is good: Current Stock' + Cast( Z.Stock as varchar(10))
        Else 'Order some more - we are below minimum stock'
        End
From (
    Select P.Stock
    From dbo.Products As P
    Where P.ProductId = 1
    Union All
    Select Value
    From ( Select 0 As Value ) As Z
    Where Not Exists    (
                        Select 1
                        From dbo.Products As P1
                        Where P1.ProductId = 1
                        )
    ) As Z

If you wanted to reuse @Stock later in your procedure then you must do it multiple statements. You cannot combine an assignment with a statement that returns data in the same statement in T-SQL. One need only try to see the error:

Declare @Foo int

Select @Foo = 42, object_id
From sys.objects

Msg 141, Level 15, State 1, Line 3 A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

edited Jan 18 '11 at 16:50

answered Jan 18 '11 at 04:31

Thomas

63,911
12
95
141

Hi Thomas- thanks for the answer. The thing is I want to reuse the @Stock parameter later in the proc :-) – Sha Jan 18 '11 at 16:29
@Perelli - Can't do it. You cannot combine an assignment with a statement that returns data in the same statement. – Thomas Jan 18 '11 at 16:37
exactly, but I was only trying to get a single value out for comparison and later usage.. – Sha Jan 18 '11 at 17:23
@Perelli - You could declare a separate variable and set it equal to the message after you set the @Stock value. However, you cannot return the text message and set @Stock in the same statement. – Thomas Jan 18 '11 at 18:05

T-SQL - parameter assignment, data retrieval and comparison in one line - is it possible?

2 Answers2