Condition in "BEGIN" - SQL Server Forums

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources