'IF EXISTS (SELECT) in SQL Server not working as expected

I have a code like this:

IF EXISTS (SELECT * FROM table WHERE id = @id)
BEGIN
    UPDATE table
    SET stock = stock + @stock
    WHERE id = @id
END
ELSE
BEGIN
    INSERT INTO [table] ([id], [name], [stock])
    VALUES (@id, @name, @stock)
END

But, this code isn't working and I am unable to find the root cause for the same. Can someone please help me?

sqlsql-serverif-statementexists


Solution 1:[1]

I do not see any error in your code, I tried to replicate the process and it is working fine for me. Can you tell me what is the error you are facing exactly.

The following is the code I tried to replicate your scenario: 

 CREATE TABLE stocks (
    id INT
    ,NAME VARCHAR(100)
    ,stock BIGINT
    )

CREATE PROCEDURE InsertStocks @id INT
    ,@name VARCHAR(100)
    ,@stock BIGINT
AS
BEGIN
    IF EXISTS (
            SELECT *
            FROM stocks
            WHERE id = @id
            )
    BEGIN
        UPDATE stocks
        SET stock = stock + @stock
        WHERE id = @id
    END
    ELSE
    BEGIN
        INSERT INTO stocks (
            [id]
            ,[name]
            ,[stock]
            )
        VALUES (
            @id
            ,@name
            ,@stock
            )
    END
END

INSERT INTO stocks
VALUES (
    1
    ,'abc'
    ,200
    )

INSERT INTO stocks
VALUES (
    2
    ,'abc'
    ,300
    )

INSERT INTO stocks
VALUES (
    3
    ,'abc'
    ,500
    )

EXEC Insertstocks 1
    ,'abc'
    ,700

This is updated successfully in my case.

Solution 2:[2]

table is a reserved keyword. so I guess you have a trivial syntax error: Incorrect syntax near the keyword 'table'. Wrap it with [], as you already did for INSERT statement

IF EXISTS (
        SELECT * FROM [table] WHERE id = @id)
BEGIN
        UPDATE  [table] SET     stock   = stock + @stock
        WHERE   id      = @id
END
ELSE
BEGIN
        INSERT INTO [table] ([id]
        ,[name]
        ,[stock])
            VALUES
            (
                @id,@name,@stock
            )
        END

Solution 3:[3]

Your code and syntax is correct. Let's see a sample example:

if EXISTS(select * from dbo.tbName where Id=1)

BEGIN
print 1
END

ELSE
BEGIN
print 2
END

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 srinioracle
Solution 2
Solution 3 Cody Gray

Related Questions