Assigning values to multiple variables (via subqueries) for use in an update

[KathyB]

Hi, figured out where I was going wrong in my post just prior, but is
there ANY way I can assign several variables to then use them in an
Update statement, for example (this does not work):

ALTER PROCEDURE dbo.UpdateXmlWF
(
@varWO varchar(50)
)
AS
DECLARE @varCust VARCHAR(50)
SELECT @varCust = (SELECT Customer FROM tblWorkOrders
WHERE WorkOrder=@varWO)

DECLARE @varAssy VARCHAR(50)
SELECT @varAssy=(SELECT Assy FROM tblWorkOrders
WHERE WorkOrder=@varWO)

UPDATE statement here using declared variables...

I can set one @variable but not multiple. Any clues? kinda new to
this.

Thanks,
Kathy

[Chuck Urwiler]

Kathy,

See my response to your previous post.

I think you want to try this instead:

DECLARE @varCust varchar(50), varAssy varchar(50)
SELECT @varCust = Customer,
@varAssy = Assy
FROM tblWorkOrders
WHERE WorkOrder = @varWO

UPDATE table
SET field = @varCust,
field2 = @varAssy
where somefield = somevalue

In fact, you could condense this to the following:

UPDATE table
SET field = tblWorkOrders.Customer,
field2 = tblWorkOrders.Assy
FROM tblWorkOrders
JOIN table ON tblWorkOrders.PK = table.FK
WHERE tblWorkOrders.WorkOrder = @varWO
AND otherconditions...

Hope this helps.

--
-Chuck Urwiler, MCSD, MCDBA
http://www.eps-software.com

[Shervin Shapourian]

Kathy,

You don't need two separate SELECT statements to assign values to your
variables. You can do it like this:

DECLARE @varCust VARCHAR(50),
@varAssy VARCHAR(50)

SELECT @varCust = Customer,
@varAssy = Assy
FROM tblWorkOrders
WHERE WorkOrder = @varWO

But I don't understand your problem with inserting. You can modify as many
columns as you want in UPDATE statement. What's your problem here?
By the way, if these variables have been declared just to use in your UPDATE
statement, then you don't really need them. You can join tblWorkOrders table
with whatever table you want update and fetch values from tblWorkOrders
directly into your destination table.

Shervin

"KathyB" <Ka**********@attbi.com> wrote in message
news:75*************************@posting.google.co m...

Hi, figured out where I was going wrong in my post just prior, but is
there ANY way I can assign several variables to then use them in an
Update statement, for example (this does not work):

ALTER PROCEDURE dbo.UpdateXmlWF
(
@varWO varchar(50)
)
AS
DECLARE @varCust VARCHAR(50)
SELECT @varCust = (SELECT Customer FROM tblWorkOrders
WHERE WorkOrder=@varWO)

DECLARE @varAssy VARCHAR(50)
SELECT @varAssy=(SELECT Assy FROM tblWorkOrders
WHERE WorkOrder=@varWO)

UPDATE statement here using declared variables...

I can set one @variable but not multiple. Any clues? kinda new to
this.

Thanks,
Kathy