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ரதீப், Aug 24 '15 at 14:45
possible duplicate of [SET versus SELECT when assigning variables?](http://stackoverflow.com/questions/3945361/set-versus-select-when-assigning-variables) — Lukasz Szozda, Aug 24 '15 at 14:46
You may also find this worth a read.http://programmers.stackexchange.com/questions/194446/how-much-business-logic-should-the-database-implement — Mark, Aug 24 '15 at 14:50
I'll try to answer your replies in one response. The result of the working query is '1'. Unfortunately 'count' is in fact the column name in the products table. I was not familiar of the differences between SET & SELECT when assigning variables (thank you lad2025). After reading the link I'm not sure that's the issue. Only because when I run my query outside of the if statement, it returns one result. — Steve H, Aug 24 '15 at 14:57

score 1 · Answer 1 · edited Aug 24 '15 at 14:56

1

You'd normally use EXISTS

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

edited Aug 24 '15 at 14:56

marc_s

732,580
175
1,330
1,459

answered Aug 24 '15 at 14:54

gbn

422,506
82
585
676

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 :) – Steve H Aug 24 '15 at 15:02

score 0 · Answer 2 · answered Aug 24 '15 at 14:48

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

score 0 · Accepted Answer · answered Aug 24 '15 at 15:05

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.

SQL Server : Not So Simple IF Statement

3 Answers3