[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

IF EXISTS (SELECT @myParameter = dbo.Something

Posted on 2007-10-05
7
Medium Priority
?
4,160 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:dtleahy
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20022582
(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
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 20022589
SELECT @param1 = x.intInvQty, @param2 = y.fk_intInvStatusID
FROM dbo.tbTableX x
JOIN dbo.tblTableY y
  on ...

IF @@ROWCOUNT > 0
BEGIN
  ...

END
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20022594
(SELECT dbo.tblInventory.intInvQty, dbo.tblInventory.fk_intInvStatusID, dbo.tblCartDetails.pk_intCartDetCartID,
dbo.tblCartDetails.pk_intCartDetLineItem FROM ...


do it like this
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Assisted Solution

by:jaanth
jaanth earned 400 total points
ID: 20022681
Dennis,

I have not seen that systax before for using exist, I have only used it inside the where clause of the select statement, which would not meet you needs here. In the past method for solving this situation is to test on rowcount.

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

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)

if @@rowcount = 1
     
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
-- =========================================

Hopw this helps,

Jaanth
0
 

Author Comment

by:dtleahy
ID: 20022954
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
0
 
LVL 7

Expert Comment

by:jaanth
ID: 20024538
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









0
 

Author Comment

by:dtleahy
ID: 20026249
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
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question