CODEWITHSUNDEEP
sqlsql-servert-sql
user1863490
user1863490

Reputation: 31

2 Variables for same column in SQL

I am struggling to understand the following section of code as it pertains to the whole script. Can someone explain the following portion of the code to me in plain English? Why have, what looks, like 2 declared variables for the one 'Seller_No' column?

SET @strSellerNo2 = NULL

SELECT  @strSellerNo2 = Seller_No
FROM    Outlet.tblProductMaster
WHERE   Product_No = @strProductNo
        AND Seller_No <> @strSellerNo1
        AND Product_Status = 'Available'

USE [OutletRetail]
GO
/****** Object:  StoredProcedure [Outlet].[sp_UpdateProductStatus]    Script Date: 01/16/2014 19:58:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

--Updates Product status codes to Available if NULL
ALTER   PROCEDURE [Outlet].[sp_UpdateProductStatus]
AS

DECLARE @strProductNo varchar(20)
DECLARE @strSellerNo1 varchar(10)
DECLARE @strSellerNo2 varchar(10)


DECLARE UpdateProductCursor CURSOR FOR

    SELECT Product_No, Seller_No
    FROM Outlet.tblProductMaster
    WHERE Product_Status IS NULL

OPEN UpdateProductCursor
FETCH NEXT FROM UpdateProductCursor INTO @strProduct_No, @strSellerNo1

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @strSellerNo2 = NULL

    SELECT  @strSellerNo2 = Seller_No
    FROM    Outlet.tblProductMaster
    WHERE   Product_No = @strProductNo
        AND Seller_No <> @strSellerNo1
        AND Product_Status = 'Available'


IF (@strSellerNo2 IS NULL)
BEGIN
        UPDATE Outlet.tblProductMaster
        SET Product_Status = 'Available'
        WHERE Product_No = @strProductNo
            AND Seller_No = @strSellerNo1
    END


UPDATE  Outlet.tblProductMaster
SET Product_Status = 'Not Available'
WHERE   Product_No = @strProductNo
        AND Seller_No <> @strSellerNo2
        AND Product_Status IS NULL

    FETCH NEXT FROM UpdateProductCursor INTO @strProductNo, @strSellerNo1
END

CLOSE UpdateProductCursor
DEALLOCATE UpdateProductCursor

Upvotes: 1

Views: 96

Answers (1)

MarkD
MarkD

Reputation: 5316

Hope this helps;

SELECT  @strSellerNo2 = Seller_No       --  Find me the Seller Number
FROM    Outlet.tblProductMaster         --  In the tblProductMaster table
WHERE   Product_No = @strProductNo      --  where the product number is the product we're looking for
    AND Seller_No <> @strSellerNo1      --  and the Seller Number is not the Seller Number previously found
    AND Product_Status = 'Available'    --  and the Product Status is "Available"

Upvotes: 2

Related Questions