SQL Server Forums
Profile | Register | 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"
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

520 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

520 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

520 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

520 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

520 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

520 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

520 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  
 New 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.22 seconds. Powered By: Snitz Forums 2000