Help with passing a value from a temp table

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 2000 Forums

SQL Server Development (2000)

Help with passing a value from a temp table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-07 : 07:01:22

Mark writes "Hi

I need help in passing a value from a temp table to a stored procedure. I want to use openxml one time to get all of my data and then pass each item to it's stored procedure.

-- Load the temp table --
INSERT INTO @tempData
SELECT *
FROM OPENXML(@hResults, '/test-environment', 1)
WITH(
Assembly1 nvarchar(255) '../@name1',
Assembly2 nvarchar(255) '../@name2',
Assembly3 nvarchar(255) '../@name3',
Assembly4 nvarchar(255) '../@name4',
Assembly5 nvarchar(255) '../@name5',
)

-- pass value to stored procedure --
exec [usp_InsertAssembly_Name] Assembly1, @AssemblyID output

^^^^^^^^
How can I pass the Assembly1
info into this stored
procedure?

Thanks"

SMerrill
Posting Yak Master

206 Posts

Posted - 2005-09-09 : 14:12:35

It seems you could say ...

DECLARE @Assembly1 NVARCHAR(255)
DECLARE @Assembly2 NVARCHAR(255)
DECLARE @Assembly3 NVARCHAR(255)
DECLARE @Assembly4 NVARCHAR(255)
DECLARE @Assembly5 NVARCHAR(255)
SELECT
@Assembly1 = Assembly1
, @Assembly2 = Assembly2
, @Assembly3 = Assembly3
, @Assembly4 = Assembly4
, @Assembly5 = Assembly5
FROM OPENXML(@hResults, '/test-environment', 1)
WITH(
Assembly1 nvarchar(255) '../@name1',
Assembly2 nvarchar(255) '../@name2',
Assembly3 nvarchar(255) '../@name3',
Assembly4 nvarchar(255) '../@name4',
Assembly5 nvarchar(255) '../@name5',
)

... to fill up the five variables with data, then ...

exec [usp_InsertAssembly_Name] @Assembly1, @AssemblyID1 output
exec [usp_InsertAssembly_Name] @Assembly2, @AssemblyID2 output
exec [usp_InsertAssembly_Name] @Assembly3, @AssemblyID3 output
exec [usp_InsertAssembly_Name] @Assembly4, @AssemblyID4 output
exec [usp_InsertAssembly_Name] @Assembly5, @AssemblyID5 output

... to pass the values to the stored procedures...

~ Shaun Merrill
Seattle, WA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources