Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Condition in "BEGIN"

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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...
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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)
Go to Top of Page

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.

Go to Top of Page

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 ???
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -