| Author |
Topic |
|
magmo
Aged Yak Warrior
558 Posts |
 Posted - 2012-11-20 : 13:07:02
|
Hi
I have a stored procedure that looks kinda like this..
@CartID nVarChar (20)
AS
BEGIN
INSERT INTO t_Order (CartID, ArticleID, Qty, FileName)
SELECT @CartID, ArticleID, Qty, FileName FROM t_Cart WHERE CaID = @CartID
UPDATE t_Articles SET InStock = = (tp.InStock - (ts.Quantity * ISNULL(tp.PackageSize,1)))
from t_Articles tp join t_Cart ts on
tp.ArticleID = ts.ArticleID AND (ts.UserID = @CartID)
DELETE FROM t_Cart WHERE UserID=@CartID
This transfers all rows from t_Cart to t_Order, update the Instock value and finaly delete the rows in t_Cart. This all works fine, but I think I need to add a condition to the Update of the Instock part.
The thing is, if there is a row and a column value in t_Cart (UseSize, Bit) that is true then another update should take place. Like this..
Update t_ArtSizes
Set InStock = (tp.InStock - ts.Qty)
from t_ArtSizes tp join t_Cart ts on
tp.SizeID = ts.SizeID
Could anyone show how that condition would look like?
|
|
|
shilpash
Posting Yak Master
103 Posts |
Posted - 2012-11-20 : 14:11:33
|
Are you tryng to update the same table t_Articles or t_ArtSizes.What I am uderstanding is if row count in t_Cart is not 0,you update instock field in t_artcles else instock in another table t_ArtSizes right.If so,use this--
CREATE PROCEDURE ProcName @CartID NVARCHAR(20)
AS
BEGIN
INSERT INTO t_Order
(CartID
,ArticleID
,Qty
,FileName)
SELECT @CartID
,ArticleID
,Qty
,FileName
FROM t_Cart
WHERE CaID = @CartID
DECLARE @t_Cart INT = (SELECT COUNT(*)
FROM t_order
)
IF @t_cart > 0
BEGIN
UPDATE t_Articles
SET InStock = (tp.InStock - (ts.Quantity * ISNULL(tp.PackageSize,1)))
FROM t_Articles tp
JOIN t_Cart ts
ON tp.ArticleID = ts.ArticleID
AND (ts.UserID = @CartID)
END
ELSE
BEGIN
UPDATE t_ArtSizes
SET InStock = (tp.InStock - ts.Qty)
FROM t_ArtSizes tp
JOIN t_Cart ts
ON tp.SizeID = ts.SizeID
END
DELETE FROM t_Cart
WHERE UserID = @CartID
END |
 |
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2012-11-20 : 14:22:21
|
| The tCart table have x number of rows, if a row with column name "UseSize" is True then I need to update the t_ArtSizes table, otherwise I should update the t_Articles table |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-20 : 15:09:39
|
You mean this :
IF (Select Count(*) from t_cart Where UserId = @CardID and UseSize = 1) > 0
Begin
Update t_ArtSizes
Set InStock = (tp.InStock - ts.Qty)
from t_ArtSizes tp join t_Cart ts on
tp.SizeID = ts.SizeID
END
ELSE
BEGIN
UPDATE t_Articles SET InStock = = (tp.InStock - (ts.Quantity * ISNULL(tp.PackageSize,1)))
from t_Articles tp join t_Cart ts on
tp.ArticleID = ts.ArticleID AND (ts.UserID = @CartID)
END |
|
|
|
shilpash
Posting Yak Master
103 Posts |
Posted - 2012-11-20 : 15:19:14
|
CREATE PROCEDURE ProcName @CartID NVARCHAR(20)
AS
BEGIN
INSERT INTO t_Order
(CartID
,ArticleID
,Qty
,FileName)
SELECT @CartID
,ArticleID
,Qty
,FileName
FROM t_Cart
WHERE CaID = @CartID
DECLARE @UseSize VARCHAR= (SELECT UseSize
FROM t_Cart
)
IF @UseSize = 'true'
BEGIN
UPDATE t_ArtSizes
SET InStock = (tp.InStock - ts.Qty)
FROM t_ArtSizes tp
JOIN t_Cart ts
ON tp.SizeID = ts.SizeID
END
ELSE
BEGIN
UPDATE t_Articles
SET InStock = (tp.InStock - (ts.Quantity * ISNULL(tp.PackageSize,1)))
FROM t_Articles tp
JOIN t_Cart ts
ON tp.ArticleID = ts.ArticleID
AND (ts.UserID = @CartID)
END
DELETE FROM t_Cart
WHERE UserID = @CartID
END |
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2012-11-20 : 16:13:07
|
quote:
Originally posted by sodeep
You mean this :
IF (Select Count(*) from t_cart Where UserId = @CardID and UseSize = 1) > 0
Begin
Update t_ArtSizes
Set InStock = (tp.InStock - ts.Qty)
from t_ArtSizes tp join t_Cart ts on
tp.SizeID = ts.SizeID
END
ELSE
BEGIN
UPDATE t_Articles SET InStock = = (tp.InStock - (ts.Quantity * ISNULL(tp.PackageSize,1)))
from t_Articles tp join t_Cart ts on
tp.ArticleID = ts.ArticleID AND (ts.UserID = @CartID)
END
Thanks, but doesn't this handle either the update of t_Articles or t_ArtSizes not a mix of them both? |
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2012-11-20 : 16:22:32
|
quote:
Originally posted by shilpash
CREATE PROCEDURE ProcName @CartID NVARCHAR(20)
AS
BEGIN
INSERT INTO t_Order
(CartID
,ArticleID
,Qty
,FileName)
SELECT @CartID
,ArticleID
,Qty
,FileName
FROM t_Cart
WHERE CaID = @CartID
DECLARE @UseSize VARCHAR= (SELECT UseSize
FROM t_Cart
)
IF @UseSize = 'true'
BEGIN
UPDATE t_ArtSizes
SET InStock = (tp.InStock - ts.Qty)
FROM t_ArtSizes tp
JOIN t_Cart ts
ON tp.SizeID = ts.SizeID
END
ELSE
BEGIN
UPDATE t_Articles
SET InStock = (tp.InStock - (ts.Quantity * ISNULL(tp.PackageSize,1)))
FROM t_Articles tp
JOIN t_Cart ts
ON tp.ArticleID = ts.ArticleID
AND (ts.UserID = @CartID)
END
DELETE FROM t_Cart
WHERE UserID = @CartID
END
Thanks but I get an error on the DECLARE @UseSize VARCHAR = (SELECT UseSize FROM t_Cart) part...
|
|
|
|
shilpash
Posting Yak Master
103 Posts |
Posted - 2012-11-20 : 16:34:34
|
| How are you defining UseSize as true.Is it UseSize = 1.if so, the query from sodeep should work |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-20 : 16:40:27
|
quote:
Originally posted by magmo
quote:
Originally posted by sodeep
You mean this :
IF (Select Count(*) from t_cart Where UserId = @CardID and UseSize = 1) > 0
Begin
Update t_ArtSizes
Set InStock = (tp.InStock - ts.Qty)
from t_ArtSizes tp join t_Cart ts on
tp.SizeID = ts.SizeID
END
ELSE
BEGIN
UPDATE t_Articles SET InStock = = (tp.InStock - (ts.Quantity * ISNULL(tp.PackageSize,1)))
from t_Articles tp join t_Cart ts on
tp.ArticleID = ts.ArticleID AND (ts.UserID = @CartID)
END
Thanks, but doesn't this handle either the update of t_Articles or t_ArtSizes not a mix of them both?
Perhaps I am not understanding your question |
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2012-11-20 : 16:48:27
|
quote:
Originally posted by shilpash
How are you defining UseSize as true.Is it UseSize = 1.if so, the query from sodeep should work
UseSize is a bit datatype column, and the rows from t_Cart can contain rows that either has UseSize = true or (UseSize = 0 or UseSize is null) |
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2012-11-20 : 17:00:46
|
quote:
Originally posted by sodeep
quote:
Originally posted by magmo
quote:
Originally posted by sodeep
You mean this :
IF (Select Count(*) from t_cart Where UserId = @CardID and UseSize = 1) > 0
Begin
Update t_ArtSizes
Set InStock = (tp.InStock - ts.Qty)
from t_ArtSizes tp join t_Cart ts on
tp.SizeID = ts.SizeID
END
ELSE
BEGIN
UPDATE t_Articles SET InStock = = (tp.InStock - (ts.Quantity * ISNULL(tp.PackageSize,1)))
from t_Articles tp join t_Cart ts on
tp.ArticleID = ts.ArticleID AND (ts.UserID = @CartID)
END
Thanks, but doesn't this handle either the update of t_Articles or t_ArtSizes not a mix of them both?
Perhaps I am not understanding your question
What I mean is that the rows from t_Cart can contains a mix, for example...
row 1 can contain UseSize = True
row 2 can contain UseSize = NULL
row 3 can contain UseSize = True
row 4 can contain UseSize = False
row 5 can contain UseSize = False
In the loop I needd to know if the current row has UseSize = True, if it does I should update t_ArtSizes otherwise I should update t_Articles
Hope that makes sence.
|
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-20 : 19:39:05
|
| When you say rows,you means row in t_cart for that userid or ??? |
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2012-11-21 : 00:51:22
|
quote:
Originally posted by sodeep
When you say rows,you means row in t_cart for that userid or ???
Yes rows in t_cart |
|
|
|
shilpash
Posting Yak Master
103 Posts |
Posted - 2012-11-21 : 10:26:59
|
IF EXISTS ( SELECT usesize
FROM T_cart
WHERE usesize = 'true' )
UPDATE t_ArtSizes
SET InStock = (tp.InStock - ts.Qty)
FROM t_ArtSizes tp
JOIN t_Cart ts
ON tp.SizeID = ts.SizeID
ELSE
UPDATE t_ArtSizes
SET InStock = (tp.InStock - ts.Qty)
FROM t_ArtSizes tp
JOIN t_Cart ts
ON tp.SizeID = ts.SizeID |
|
|
|
shilpash
Posting Yak Master
103 Posts |
Posted - 2012-11-21 : 10:42:21
|
Sorry ,you mentioned its a bit,so use this--
replace 'true' to 1.
IF EXISTS ( SELECT usesize FROM T_cart WHERE usesize = 1 )
UPDATE t_ArtSizes
SET InStock = (tp.InStock - ts.Qty)
FROM t_ArtSizes tp
JOIN t_Cart ts
ON tp.SizeID = ts.SizeID
ELSE
UPDATE t_ArtSizes
SET InStock = (tp.InStock - ts.Qty)
FROM t_ArtSizes tp
JOIN t_Cart ts
ON tp.SizeID = ts.SizeID |
|
|
|
|
Condition in "BEGIN"