SQL Server : Not So Simple IF Statement

Question

I'm trying to perform a simple IF statement. I have a table named 'products' that has a column count which is a current count of that specific product in stock. The count column in the database is of data type INT.

I can only get this to work if I statically assign the variable @count. If I assign a select statement to @count the script fails. Even though running the query alone will return a result.

Working query:

DECLARE @count INT
SET @count = 2

IF (@count > 1)
    BEGIN
        PRINT 'It works!'
    END

Failing query:

DECLARE @count INT
SET @count = (SELECT TOP 1 count from products WHERE count > 1)

IF (@count > 1)
    BEGIN
        PRINT 'It works!'
    END

SELECT TOP 1 count from products WHERE count > 1 What is the result when you run this query — Pரதீப்
– Pரதீப், Commented Aug 24, 2015 at 14:45
possible duplicate of SET versus SELECT when assigning variables? — Lukasz Szozda
– Lukasz Szozda, Commented Aug 24, 2015 at 14:46
You may also find this worth a read.programmers.stackexchange.com/questions/194446/… — Mark
– Mark, Commented Aug 24, 2015 at 14:50

marc_s · Accepted Answer · 2015-08-24 14:56:04Z

1

You'd normally use EXISTS

IF EXISTS (SELECT * from products WHERE count > 1)
    BEGIN
        PRINT 'It works!'
    END

edited Aug 24, 2015 at 14:56

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 24, 2015 at 14:54

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steve H Over a year ago

Yes and this query is expected to act slightly different. My end result is that I need all rows where [count] > 1 to be inserted (duplicate rows) and have the total count dropped by one. I was simply attempting to break down my question to the lowest level :)

A_Sk · Accepted Answer · 2015-08-24 15:05:52Z

0

Try:

DECLARE @count INT
SET @count = isnull((SELECT TOP 1 [count] from products WHERE [count] > 1),0)
IF (@count > 1)
    BEGIN
        PRINT 'It works!'
    END

count is a SQL Keyword that's why you need to use [] when you are using any keywords as a column Name.

answered Aug 24, 2015 at 15:05

A_Sk

4,6505 gold badges31 silver badges55 bronze badges

Comments

M.Ali · Accepted Answer · 2015-08-24 14:48:25Z

0

Instead of having set= select simply use select to assign value to variable also COUNTis a key word in sql server use square brackets [] around it if you want sql server to treat it as an object name..... try this....

DECLARE @count INT
SELECT TOP 1 @count =  [count] from products WHERE count > 1

IF (@count > 1)
    BEGIN
        PRINT 'It works!'
    END

answered Aug 24, 2015 at 14:48

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Collectives™ on Stack Overflow

SQL Server : Not So Simple IF Statement

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related