CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
ErikL
ErikL

Reputation: 2041

Microsoft sql stored procedure not executing select statement properly

I'm pretty new to Microsoft T-sql (Use to Oracle PL/SQL) and I ran into a annoying problem with a very simple procedure.

I created the following procedure 

CREATE PROCEDURE [dbo].[ele_test] @productId INT
AS
DECLARE @productCode VARCHAR(100);
DECLARE @productDescription VARCHAR(100);
DECLARE @error VARCHAR(100);

--Fetch product 
IF @productId != NULL
BEGIN
    SELECT @productCode = ProductCode
        ,@productDescription = ProductDescription
    FROM d_afterpay.dbo.Product
    WHERE ProductId = @productId
END

IF @@ROWCOUNT = 0
BEGIN
    SET @error = 'Product not found: ' + cast(@productId AS VARCHAR(19))

    RAISERROR (@error,16,1);
END

And when I run it this way:

exec ele_test 5

I get:

Msg 50000, Level 16, State 1, Procedure ele_test, Line 20
Product not found. Productid : 5

Yet when I run just the query like this:

SELECT * FROM d_afterpay.dbo.Product
        WHERE ProductId = 5

I do get a proper result... Any idea what I am doing wrong?

Upvotes: 1

Views: 1490

Answers (1)

connectedsoftware
connectedsoftware

Reputation: 7087

Your query syntax is slightly wrong, change the query to read:

IF (@productId IS NOT NULL)

instead of using !=

This meant your SELECT statement was never being called hence why the product was always missing.

Upvotes: 5

Related Questions