Link to home
Start Free TrialLog in
Avatar of dtleahy
dtleahyFlag for United States of America

asked on

IF EXISTS (SELECT @myParameter = dbo.Something

I cannot figure out the correct syntax for a SELECT statement that both checks for EXISTS and , if a record is found, assigns values to parameters. Can this be done? Or, does this require 2 separate SQL statements, one to check for the IF EXISTS, and one to make multiple assignments?

Here's a pseudo-example of the beginning of the statement:
IF EXISTS (SELECT @param1 = dbo.tbTableX.intInvQty, @param2 = dbo.tblTableY.fk_intInvStatusID


Here's my attempted actual SQL syntax to create the procedure (I'm getting an error stating "Incorrect syntax near '='.", pointing me to the line containing SELECT. (If I remove the IF EXISTS and the parameter assignments and just run the SELECT as a View with known good data rather than parameters, it runs fine.)

-- =========================================
CREATE PROCEDURE [dbo].[UpdateCartItemQty]
--input parameters:
@paramCartID smallint,
@paramLineItem tinyint,
@paramReqQty smallint,
@paramItemID int,
@paramUserID smallint

AS
BEGIN
      
SET NOCOUNT ON;      
            
DECLARE @param7IntAvailQty smallint, @param7Status tinyint, @param7CartID smallint, @param7LineItem tinyint

IF EXISTS (SELECT @param7IntAvailQty = dbo.tblInventory.intInvQty, @param7Status = dbo.tblInventory.fk_intInvStatusID, @param7CartID = dbo.tblCartDetails.pk_intCartDetCartID,
@param7LineItem = dbo.tblCartDetails.pk_intCartDetLineItem

FROM dbo.tblCarts INNER JOIN
dbo.tblCartDetails ON dbo.tblCarts.pk_intCartID = dbo.tblCartDetails.pk_intCartDetCartID INNER JOIN
dbo.tblInventory INNER JOIN
dbo.tblInvItemStatus ON dbo.tblInventory.fk_intInvStatusID = dbo.tblInvItemStatus.pk_intInvItemStatusID ON
dbo.tblCartDetails.fk_intCartDetInvItemID = dbo.tblInventory.pk_intInvItemID

WHERE (dbo.tblCartDetails.fk_intCartDetInvItemID = @paramItemID) AND (dbo.tblInventory.fk_intInvStatusID = 3) OR
(dbo.tblCartDetails.fk_intCartDetInvItemID = @paramItemID) AND (dbo.tblInventory.fk_intInvStatusID = 2) AND (dbo.tblInventory.intInvQty > 1) OR
(dbo.tblCartDetails.fk_intCartDetInvItemID = @paramItemID) AND (dbo.tblInventory.fk_intInvStatusID = 1) AND (dbo.tblInventory.intInvQty > 1) AND (dbo.tblInventory.fk_intInvAllocatedToID = @paramUserID))       
BEGIN
--determine if the item quantity is updatable beyond the current qty (stock items only)
-- status 0=sold, 1=on hold, 2=available, 3=stock
--if the status is 3 (stock), the user can pick any positive quantity
IF @param7Status < 3
BEGIN
IF @paramReqQty > @param7IntAvailQty
BEGIN
-- requested quantity can be no greater than the actual available quantity:
SET @paramReqQty = @param7IntAvailQty
END
END
                  
--now, update the quantity of the cart item:
UPDATE dbo.tblCartDetails
SET  intCartDetQty=@paramReqQty
WHERE ((pk_intCartDetCartID=@param7CartID) AND (pk_intCartDetLineItem=@param7LineItem))
                  
END

END
-- =========================================

Thanks in advance for any help!
-Dennis
Avatar of SQL_SERVER_DBA
SQL_SERVER_DBA
Flag of United States of America image

(SELECT @param7IntAvailQty = dbo.tblInventory.intInvQty, @param7Status = dbo.tblInventory.fk_intInvStatusID, @param7CartID = dbo.tblCartDetails.pk_intCartDetCartID,
@param7LineItem = dbo.tblCartDetails.pk_intCartDetLineItem


You are suppost to compare these values in a WHERE <statement>, you sql syntax is wrong...
please list the fields you want retrieved, tables, pk...i will write if for you
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
(SELECT dbo.tblInventory.intInvQty, dbo.tblInventory.fk_intInvStatusID, dbo.tblCartDetails.pk_intCartDetCartID,
dbo.tblCartDetails.pk_intCartDetLineItem FROM ...


do it like this
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dtleahy

ASKER

Thank all 3 of you very much for the VERY rapid reply!

I guess an "IF EXISTS (SELECT..."  may not contain assignments.

AngelIII had the answer in less than 5 minutes, thank you very much!

Jaanth did supply one additional modification of AngelIII's solution: testing the RowCount to equal 1, rather than being larger than 0 (it may be a moot point because either no rows or one row should be returned, but it is a good failsafe.)

Thank you!

Dennis

Dennis
Avatar of jaanth
jaanth

Dennis,

It is amaxing the how quick responses can be sometimes. When I started typing, there were none and by the time of posting there were all already two others.

Since you are selecting into a single set of variables, you want to make sure you only got one row back. If you think your query will ever bring back more than one, you could save off the rowcount (the reference to @@rowcount is only available one time)  and test it separately either with a case statement or second if.  If you have more than one row, raise an error.  

like this,
declare @testrowcount int

select ....
set @testrowcount = @@rowcount

if @testrowcount = 1 ...do stuff

if @testrowcount > 1 ... raise error

Thanks for splitting the points.

jaanth









Avatar of dtleahy

ASKER

Thanks for the additional input, Jaanth.

In this case, there are other stored procedures that add items to and delete items from the shopping cart, and those stored procedures prevent any item from being listed more than once (the quantity is incremented if an identical item is added to the cart.) So, Angel III's solution should work perfectly 100% of the time, and your idea of testing for exactly one row returned should never be necessary, but I thought it was a good failsafe - with no additional overhead.

Dennis