dtleahy
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_intInvSta tusID
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_intInv StatusID, @param7CartID = dbo.tblCartDetails.pk_intC artDetCart ID,
@param7LineItem = dbo.tblCartDetails.pk_intC artDetLine Item
FROM dbo.tblCarts INNER JOIN
dbo.tblCartDetails ON dbo.tblCarts.pk_intCartID = dbo.tblCartDetails.pk_intC artDetCart ID INNER JOIN
dbo.tblInventory INNER JOIN
dbo.tblInvItemStatus ON dbo.tblInventory.fk_intInv StatusID = dbo.tblInvItemStatus.pk_in tInvItemSt atusID ON
dbo.tblCartDetails.fk_intC artDetInvI temID = dbo.tblInventory.pk_intInv ItemID
WHERE (dbo.tblCartDetails.fk_int CartDetInv ItemID = @paramItemID) AND (dbo.tblInventory.fk_intIn vStatusID = 3) OR
(dbo.tblCartDetails.fk_int CartDetInv ItemID = @paramItemID) AND (dbo.tblInventory.fk_intIn vStatusID = 2) AND (dbo.tblInventory.intInvQt y > 1) OR
(dbo.tblCartDetails.fk_int CartDetInv ItemID = @paramItemID) AND (dbo.tblInventory.fk_intIn vStatusID = 1) AND (dbo.tblInventory.intInvQt y > 1) AND (dbo.tblInventory.fk_intIn vAllocated ToID = @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=@par am7CartID) AND (pk_intCartDetLineItem=@pa ram7LineIt em))
END
END
-- ========================== ========== =====
Thanks in advance for any help!
-Dennis
Here's a pseudo-example of the beginning of the statement:
IF EXISTS (SELECT @param1 = dbo.tbTableX.intInvQty, @param2 = dbo.tblTableY.fk_intInvSta
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
@param7LineItem = dbo.tblCartDetails.pk_intC
FROM dbo.tblCarts INNER JOIN
dbo.tblCartDetails ON dbo.tblCarts.pk_intCartID = dbo.tblCartDetails.pk_intC
dbo.tblInventory INNER JOIN
dbo.tblInvItemStatus ON dbo.tblInventory.fk_intInv
dbo.tblCartDetails.fk_intC
WHERE (dbo.tblCartDetails.fk_int
(dbo.tblCartDetails.fk_int
(dbo.tblCartDetails.fk_int
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=@par
END
END
-- ==========================
Thanks in advance for any help!
-Dennis
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
(SELECT dbo.tblInventory.intInvQty , dbo.tblInventory.fk_intInv StatusID, dbo.tblCartDetails.pk_intC artDetCart ID,
dbo.tblCartDetails.pk_intC artDetLine Item FROM ...
do it like this
dbo.tblCartDetails.pk_intC
do it like this
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
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
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
@param7LineItem = dbo.tblCartDetails.pk_intC
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