Logic Building Problem

Hi

I am having some problem with the logic building block of the following sproc. While using the Stock column, it raises an error saying invalid column.

Code Snippet

ALTER PROCEDURE [dbo].[usp_Issue]

@.ProductIDint,

@.IssueQtyint

AS

BEGIN

SELECTStock= ( ISNULL (SUM(R.ActualQty), 0) - ISNULL (SUM(I.IssueQty), 0))

FROM Products P

LEFT JOIN Receives R

ON P.ProductID = R.ProductID

LEFT JOIN Issues I

ON P.ProductID = I.ProductID

WHERE P.ProductID = @.ProductID

GROUP BY P.ProductID

IF Stock IS NULL

-- Error 'Invalid Product'

ELSE IF Stock < @.IssueQty

-- Error 'Not enough stock'

ELSE

-- INSER INTO ISSUES..

END

Anyone please help me writing the logical block.

Regards

Kapalic

You have to assign the value in the varaible for logical validation..

Code Snippet

ALTER PROCEDURE [dbo].[usp_Issue]

@.ProductID int,

@.IssueQty int

AS

BEGIN

Declare @.Stock as Int;

SELECT @.Stock = ( ISNULL (SUM(R.ActualQty), 0) - ISNULL (SUM(I.IssueQty), 0))

FROM Products P

LEFT JOIN Receives R

ON P.ProductID = R.ProductID

LEFT JOIN Issues I

ON P.ProductID = I.ProductID

WHERE P.ProductID = @.ProductID

GROUP BY P.ProductID

IF @.Stock IS NULL

-- Error 'Invalid Product'

Raiserror 'Invalid Product', 16, 1

ELSE IF @.Stock < @.IssueQty

-- Error 'Not enough stock'

Raiserror 'Not Enough Stock', 16, 1

ELSE

-- INSER INTO ISSUES..

END