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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Condition in "BEGIN"
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

558 Posts

Posted - 11/20/2012 :  13:07:02  Show Profile  Reply with Quote
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 - 11/20/2012 :  14:11:33  Show Profile  Reply with Quote
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 - 11/20/2012 :  14:22:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/20/2012 :  15:09:39  Show Profile  Reply with Quote
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

Edited by - sodeep on 11/20/2012 15:11:21
Go to Top of Page

shilpash
Posting Yak Master

103 Posts

Posted - 11/20/2012 :  15:19:14  Show Profile  Reply with Quote
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 - 11/20/2012 :  16:13:07  Show Profile  Reply with Quote
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 - 11/20/2012 :  16:22:32  Show Profile  Reply with Quote
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 - 11/20/2012 :  16:34:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/20/2012 :  16:40:27  Show Profile  Reply with Quote
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 - 11/20/2012 :  16:48:27  Show Profile  Reply with Quote
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 - 11/20/2012 :  17:00:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/20/2012 :  19:39:05  Show Profile  Reply with Quote
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 - 11/21/2012 :  00:51:22  Show Profile  Reply with Quote
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 - 11/21/2012 :  10:26:59  Show Profile  Reply with Quote
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 - 11/21/2012 :  10:42:21  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000