Passing IN() values as parameter

Hi...

I'm creating a procedure to fetch rows from table. One field will come come as IN(). Its the condition. That field is numeric field (note down), i would like to pass the In values as parameter.

eg: procedurename @.fieldvalue varchar(100)
as
begin
...

WHERE fieldname IN(@.fieldvalue)

while executing the procedure how to pass the value... or procedure itself has problem...?
Help me...

Tx in Advance...Once again dynamic SQL seems to be the deal here, unless a more complete description of the problem is available.
The thing here is that you cannot do a procedure that works in the way you describe without using dynamic SQL, because of the datatype clash.

Your IN clause wants to do comparisons with integers, and therefore your argument to the procedure will never be able to work unless you convert your values in the fieldvalue variable to a string which you add to the the last part of your existing query, and execute it with dynamic SQL. I cannot judge whether this is the right decision to do in your particular case without more information about the problem. Hope I got your thinking going atleast ...|||In your procedure, parse the parameter into a temp table. Join your temp table to the production table to limit the result set. Works every time, and often runs much faster than the dynamic SQL solution.

-PatP

Passing array to stored procedure

Can some one tell me the way to pass Table or Array or list of values to stored procedure?

[Note: I already know some ways which are not useful in my case:

- By passing a list of values separting each with a delimeter like comma(,)

- Using XML

]

Any other way?

Please, its urgent

Thanks in advance

http://www.google.de/search?hl=de&q=array+stored+procedure&meta=

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens...

The reference sites you provided mentioned things I already tried...

Please can you show me other ways to do it...

Cause my project has bulky transactions.... So these methods won't work here....

I need to pass Entire Table or Array from C# (Front end) to stored procedure...

and also multiple user (more than 100 concurrent users) access same resources at the same time...So I cannot use XML (file systems) for this task.

I need to pass these data in other ways...

Please help me out...

|||

How about creating a table in the database which has two fields (userId, value), assuming that the lowest granularity of the concurrence is at user level, and filling it with all the data you want to pass and call the stored procedure with userId as a parameter.

The stored procedure reads the values from the table where userId = @.userId and deletes the values at the end to keep the table clean.

|||A simple row can be passed using a comma delimited string and splitting this on the server to the appriopate values. For more than one row you would have to use a more sophisticated funtionality. The lat option would be to use something like a materialized table, as the previous poster already mentioned.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks Jens and ,

I tried as you said, I created a class in C# which creates a global temporary table in SQL Server, then I executed Stored procedure which manipulates that temporary table, and finally when stored procedure does its work, this class deletes the temporary table.

To avoid conflicts in session and user from using same table, I suffixed the table with date time when the table was created....

This table is dynamic, it can be created by passing DataTable from C#....

It solved my problem....

Thanks both of you for giving me idea....

|||

Hi,

Jens and Lakshmana

The Idea you guys gave me helped me a lot.

I created a class (in C#) that first creates a global temporary table in SQL Server, then executes stored procedure. This procedure

manipulates the temporary table and takes data stored in this table. When stored procedure finishes its task, the class in C#, deletes the temporary table.

To avoid conflict in sessions and multiple users from using same table, I suffixed the table with date time when the table is being created...

This helped me a lot (though it is a negative from performance point of view)...

Thank you guys....

|||Hi All|||

Hi All

thnx for ur comments

really its all Usefull

i Just Have small Problem .

after i Created the global temporary table in SQL Server , and Inserted the Data i wanna use in the Stored Procedure

i Pass the temporary table Name to the Stored Procedure

But i couldnt use it with aCursor or even Normal Select Statement.

coz i have to concatinate it in the select Statemant

Searchin For Help

Thanks for all of u

|||Hi All.
This is one problem that kept on following me. I tried all the other suggestions and it worked fine at the time. Recently I decided to look for an alternative that will give me more flexibility.
My problem was I need to pass a list of keys to a stored proc (sp). The system I work on was architectured to work over slow networks and the internet, so no unnecessary call to the backend, hence my need to pass a list of keys.

I pass my keys in as a delimeted string (delimeter can be "," or "|" or any chosen character, this will be more clear later).
On the database I have a table-value function that convert this delimeted string to a table and this allows me to use it in joins and "where fk_somekey in (select * strval from delimtable)" statements.
Here is the function:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Lucian@.Probia.co.za
-- Create date: 2006/11/22
-- Description: Returns table for delimited string
-- =============================================
Create FUNCTION [dbo].[DelimStrToTable]
(
-- Add the parameters for the function here
@.delimStr varchar(8000),
@.delimchar char
)
RETURNS
@.StrValTable TABLE
(
-- Add the column definitions for the TABLE variable here
StrVal varchar(1000)

)
AS
BEGIN
-- Fill the table variable with the rows for your result set
declare @.strlist varchar(8000), @.pos int, @.delim char, @.lstr varchar(1000)
set @.strlist = @.delimStr
set @.delim = @.delimchar

while ((len(@.strlist) > 0) and (@.strlist <> ''))
begin
set @.pos = charindex(@.delim, @.strlist)

if @.pos > 0
begin
set @.lstr = substring(@.strlist, 1, @.pos-1)
set @.strlist = ltrim(substring(@.strlist,charindex(@.delim, @.strlist)+1, 8000))
end
else
begin
set @.lstr = @.strlist
set @.strlist = ''
end
Insert @.StrValTable values (@.lstr)
--print @.lstr
end

RETURN
END

and now I can do this:

Select * from Authors where AuthorId in (select convert(int,strval) from DelimStrToTable('1|2|11|45', '|'))

or
Select A.* from Authors A inner join DelimStrToTable('1|2|11|45', '|') as IDLst
on A.AuthorId = convert(int,IDLst.strval)

One is of course limited by the size of stored proc parameter. For most of my scenarios, this worked fine. Hope it serves as an alternative.

Lucian|||I think the line
StrVal varchar(1000)
can be changed to
StrVal varchar(10)
The size here depend on the size of your list item in the parameter. Size of 10 is fine for a list of integers.
sql

Passing array to stored procedure

Can some one tell me the way to pass Table or Array or list of values to stored procedure?

[Note: I already know some ways which are not useful in my case:

- By passing a list of values separting each with a delimeter like comma(,)

- Using XML

]

Any other way?

Please, its urgent

Thanks in advance

http://www.google.de/search?hl=de&q=array+stored+procedure&meta=

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens...

The reference sites you provided mentioned things I already tried...

Please can you show me other ways to do it...

Cause my project has bulky transactions.... So these methods won't work here....

I need to pass Entire Table or Array from C# (Front end) to stored procedure...

and also multiple user (more than 100 concurrent users) access same resources at the same time...So I cannot use XML (file systems) for this task.

I need to pass these data in other ways...

Please help me out...

|||

How about creating a table in the database which has two fields (userId, value), assuming that the lowest granularity of the concurrence is at user level, and filling it with all the data you want to pass and call the stored procedure with userId as a parameter.

The stored procedure reads the values from the table where userId = @.userId and deletes the values at the end to keep the table clean.

|||A simple row can be passed using a comma delimited string and splitting this on the server to the appriopate values. For more than one row you would have to use a more sophisticated funtionality. The lat option would be to use something like a materialized table, as the previous poster already mentioned.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks Jens and ,

I tried as you said, I created a class in C# which creates a global temporary table in SQL Server, then I executed Stored procedure which manipulates that temporary table, and finally when stored procedure does its work, this class deletes the temporary table.

To avoid conflicts in session and user from using same table, I suffixed the table with date time when the table was created....

This table is dynamic, it can be created by passing DataTable from C#....

It solved my problem....

Thanks both of you for giving me idea....

|||

Hi,

Jens and Lakshmana

The Idea you guys gave me helped me a lot.

I created a class (in C#) that first creates a global temporary table in SQL Server, then executes stored procedure. This procedure

manipulates the temporary table and takes data stored in this table. When stored procedure finishes its task, the class in C#, deletes the temporary table.

To avoid conflict in sessions and multiple users from using same table, I suffixed the table with date time when the table is being created...

This helped me a lot (though it is a negative from performance point of view)...

Thank you guys....

|||Hi All|||

Hi All

thnx for ur comments

really its all Usefull

i Just Have small Problem .

after i Created the global temporary table in SQL Server , and Inserted the Data i wanna use in the Stored Procedure

i Pass the temporary table Name to the Stored Procedure

But i couldnt use it with aCursor or even Normal Select Statement.

coz i have to concatinate it in the select Statemant

Searchin For Help

Thanks for all of u

|||Hi All.
This is one problem that kept on following me. I tried all the other suggestions and it worked fine at the time. Recently I decided to look for an alternative that will give me more flexibility.
My problem was I need to pass a list of keys to a stored proc (sp). The system I work on was architectured to work over slow networks and the internet, so no unnecessary call to the backend, hence my need to pass a list of keys.

I pass my keys in as a delimeted string (delimeter can be "," or "|" or any chosen character, this will be more clear later).
On the database I have a table-value function that convert this delimeted string to a table and this allows me to use it in joins and "where fk_somekey in (select * strval from delimtable)" statements.
Here is the function:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Lucian@.Probia.co.za
-- Create date: 2006/11/22
-- Description: Returns table for delimited string
-- =============================================
Create FUNCTION [dbo].[DelimStrToTable]
(
-- Add the parameters for the function here
@.delimStr varchar(8000),
@.delimchar char
)
RETURNS
@.StrValTable TABLE
(
-- Add the column definitions for the TABLE variable here
StrVal varchar(1000)

)
AS
BEGIN
-- Fill the table variable with the rows for your result set
declare @.strlist varchar(8000), @.pos int, @.delim char, @.lstr varchar(1000)
set @.strlist = @.delimStr
set @.delim = @.delimchar

while ((len(@.strlist) > 0) and (@.strlist <> ''))
begin
set @.pos = charindex(@.delim, @.strlist)

if @.pos > 0
begin
set @.lstr = substring(@.strlist, 1, @.pos-1)
set @.strlist = ltrim(substring(@.strlist,charindex(@.delim, @.strlist)+1, 8000))
end
else
begin
set @.lstr = @.strlist
set @.strlist = ''
end
Insert @.StrValTable values (@.lstr)
--print @.lstr
end

RETURN
END

and now I can do this:

Select * from Authors where AuthorId in (select convert(int,strval) from DelimStrToTable('1|2|11|45', '|'))

or
Select A.* from Authors A inner join DelimStrToTable('1|2|11|45', '|') as IDLst
on A.AuthorId = convert(int,IDLst.strval)

One is of course limited by the size of stored proc parameter. For most of my scenarios, this worked fine. Hope it serves as an alternative.

Lucian|||I think the line
StrVal varchar(1000)
can be changed to
StrVal varchar(10)
The size here depend on the size of your list item in the parameter. Size of 10 is fine for a list of integers.

Passing array to stored procedure

Can some one tell me the way to pass Table or Array or list of values to stored procedure?

[Note: I already know some ways which are not useful in my case:

- By passing a list of values separting each with a delimeter like comma(,)

- Using XML

]

Any other way?

Please, its urgent

Thanks in advance

http://www.google.de/search?hl=de&q=array+stored+procedure&meta=

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens...

The reference sites you provided mentioned things I already tried...

Please can you show me other ways to do it...

Cause my project has bulky transactions.... So these methods won't work here....

I need to pass Entire Table or Array from C# (Front end) to stored procedure...

and also multiple user (more than 100 concurrent users) access same resources at the same time...So I cannot use XML (file systems) for this task.

I need to pass these data in other ways...

Please help me out...

|||

How about creating a table in the database which has two fields (userId, value), assuming that the lowest granularity of the concurrence is at user level, and filling it with all the data you want to pass and call the stored procedure with userId as a parameter.

The stored procedure reads the values from the table where userId = @.userId and deletes the values at the end to keep the table clean.

|||A simple row can be passed using a comma delimited string and splitting this on the server to the appriopate values. For more than one row you would have to use a more sophisticated funtionality. The lat option would be to use something like a materialized table, as the previous poster already mentioned.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks Jens and ,

I tried as you said, I created a class in C# which creates a global temporary table in SQL Server, then I executed Stored procedure which manipulates that temporary table, and finally when stored procedure does its work, this class deletes the temporary table.

To avoid conflicts in session and user from using same table, I suffixed the table with date time when the table was created....

This table is dynamic, it can be created by passing DataTable from C#....

It solved my problem....

Thanks both of you for giving me idea....

|||

Hi,

Jens and Lakshmana

The Idea you guys gave me helped me a lot.

I created a class (in C#) that first creates a global temporary table in SQL Server, then executes stored procedure. This procedure

manipulates the temporary table and takes data stored in this table. When stored procedure finishes its task, the class in C#, deletes the temporary table.

To avoid conflict in sessions and multiple users from using same table, I suffixed the table with date time when the table is being created...

This helped me a lot (though it is a negative from performance point of view)...

Thank you guys....

|||Hi All|||

Hi All

thnx for ur comments

really its all Usefull

i Just Have small Problem .

after i Created the global temporary table in SQL Server , and Inserted the Data i wanna use in the Stored Procedure

i Pass the temporary table Name to the Stored Procedure

But i couldnt use it with aCursor or even Normal Select Statement.

coz i have to concatinate it in the select Statemant

Searchin For Help

Thanks for all of u

|||Hi All.
This is one problem that kept on following me. I tried all the other suggestions and it worked fine at the time. Recently I decided to look for an alternative that will give me more flexibility.
My problem was I need to pass a list of keys to a stored proc (sp). The system I work on was architectured to work over slow networks and the internet, so no unnecessary call to the backend, hence my need to pass a list of keys.

I pass my keys in as a delimeted string (delimeter can be "," or "|" or any chosen character, this will be more clear later).
On the database I have a table-value function that convert this delimeted string to a table and this allows me to use it in joins and "where fk_somekey in (select * strval from delimtable)" statements.
Here is the function:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Lucian@.Probia.co.za
-- Create date: 2006/11/22
-- Description: Returns table for delimited string
-- =============================================
Create FUNCTION [dbo].[DelimStrToTable]
(
-- Add the parameters for the function here
@.delimStr varchar(8000),
@.delimchar char
)
RETURNS
@.StrValTable TABLE
(
-- Add the column definitions for the TABLE variable here
StrVal varchar(1000)

)
AS
BEGIN
-- Fill the table variable with the rows for your result set
declare @.strlist varchar(8000), @.pos int, @.delim char, @.lstr varchar(1000)
set @.strlist = @.delimStr
set @.delim = @.delimchar

while ((len(@.strlist) > 0) and (@.strlist <> ''))
begin
set @.pos = charindex(@.delim, @.strlist)

if @.pos > 0
begin
set @.lstr = substring(@.strlist, 1, @.pos-1)
set @.strlist = ltrim(substring(@.strlist,charindex(@.delim, @.strlist)+1, 8000))
end
else
begin
set @.lstr = @.strlist
set @.strlist = ''
end
Insert @.StrValTable values (@.lstr)
--print @.lstr
end

RETURN
END

and now I can do this:

Select * from Authors where AuthorId in (select convert(int,strval) from DelimStrToTable('1|2|11|45', '|'))

or
Select A.* from Authors A inner join DelimStrToTable('1|2|11|45', '|') as IDLst
on A.AuthorId = convert(int,IDLst.strval)

One is of course limited by the size of stored proc parameter. For most of my scenarios, this worked fine. Hope it serves as an alternative.

Lucian|||I think the line
StrVal varchar(1000)
can be changed to
StrVal varchar(10)
The size here depend on the size of your list item in the parameter. Size of 10 is fine for a list of integers.

Passing array to stored procedure

Can some one tell me the way to pass Table or Array or list of values to stored procedure?

[Note: I already know some ways which are not useful in my case:

- By passing a list of values separting each with a delimeter like comma(,)

- Using XML

]

Any other way?

Please, its urgent

Thanks in advance

http://www.google.de/search?hl=de&q=array+stored+procedure&meta=

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens...

The reference sites you provided mentioned things I already tried...

Please can you show me other ways to do it...

Cause my project has bulky transactions.... So these methods won't work here....

I need to pass Entire Table or Array from C# (Front end) to stored procedure...

and also multiple user (more than 100 concurrent users) access same resources at the same time...So I cannot use XML (file systems) for this task.

I need to pass these data in other ways...

Please help me out...

|||

How about creating a table in the database which has two fields (userId, value), assuming that the lowest granularity of the concurrence is at user level, and filling it with all the data you want to pass and call the stored procedure with userId as a parameter.

The stored procedure reads the values from the table where userId = @.userId and deletes the values at the end to keep the table clean.

|||A simple row can be passed using a comma delimited string and splitting this on the server to the appriopate values. For more than one row you would have to use a more sophisticated funtionality. The lat option would be to use something like a materialized table, as the previous poster already mentioned.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks Jens and ,

I tried as you said, I created a class in C# which creates a global temporary table in SQL Server, then I executed Stored procedure which manipulates that temporary table, and finally when stored procedure does its work, this class deletes the temporary table.

To avoid conflicts in session and user from using same table, I suffixed the table with date time when the table was created....

This table is dynamic, it can be created by passing DataTable from C#....

It solved my problem....

Thanks both of you for giving me idea....

|||

Hi,

Jens andLakshmana

The Idea you guys gave me helped me a lot.

I created a class (in C#) that first creates a global temporary table in SQL Server, then executes stored procedure. This procedure

manipulates the temporary table and takes data stored in this table. When stored procedure finishes its task, the class in C#, deletes the temporary table.

To avoid conflict in sessions and multiple users from using same table, I suffixed the table with date time when the table is being created...

This helped me a lot (though it is a negative from performance point of view)...

Thank you guys....

|||Hi All|||

Hi All

thnx for ur comments

really its all Usefull

i Just Have small Problem .

after i Created the global temporary table in SQL Server , and Inserted the Data i wanna use in the Stored Procedure

i Pass the temporary table Name to the Stored Procedure

But i couldnt use it with aCursor or even Normal Select Statement.

coz i have to concatinate it in the select Statemant

Searchin For Help

Thanks for all of u

|||Hi All.
This is one problem that kept on following me. I tried all the other suggestions and it worked fine at the time. Recently I decided to look for an alternative that will give me more flexibility.
My problem was I need to pass a list of keys to a stored proc (sp). The system I work on was architectured to work over slow networks and the internet, so no unnecessary call to the backend, hence my need to pass a list of keys.

I pass my keys in as a delimeted string (delimeter can be "," or "|" or any chosen character, this will be more clear later).
On the database I have a table-value function that convert this delimeted string to a table and this allows me to use it in joins and "where fk_somekey in (select * strval from delimtable)" statements.
Here is the function:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Lucian@.Probia.co.za
-- Create date: 2006/11/22
-- Description: Returns table for delimited string
-- =============================================
Create FUNCTION [dbo].[DelimStrToTable]
(
-- Add the parameters for the function here
@.delimStr varchar(8000),
@.delimchar char
)
RETURNS
@.StrValTable TABLE
(
-- Add the column definitions for the TABLE variable here
StrVal varchar(1000)

)
AS
BEGIN
-- Fill the table variable with the rows for your result set
declare @.strlist varchar(8000), @.pos int, @.delim char, @.lstr varchar(1000)
set @.strlist = @.delimStr
set @.delim = @.delimchar

while ((len(@.strlist) > 0) and (@.strlist <> ''))
begin
set @.pos = charindex(@.delim, @.strlist)

if @.pos > 0
begin
set @.lstr = substring(@.strlist, 1, @.pos-1)
set @.strlist = ltrim(substring(@.strlist,charindex(@.delim, @.strlist)+1, 8000))
end
else
begin
set @.lstr = @.strlist
set @.strlist = ''
end
Insert @.StrValTable values (@.lstr)
--print @.lstr
end

RETURN
END

and now I can do this:

Select * from Authors where AuthorId in (select convert(int,strval) from DelimStrToTable('1|2|11|45', '|'))

or
Select A.* from Authors A inner join DelimStrToTable('1|2|11|45', '|') as IDLst
on A.AuthorId = convert(int,IDLst.strval)

One is of course limited by the size of stored proc parameter. For most of my scenarios, this worked fine. Hope it serves as an alternative.

Lucian

|||I think the line
StrVal varchar(1000)
can be changed to
StrVal varchar(10)
The size here depend on the size of your list item in the parameter. Size of 10 is fine for a list of integers.

Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post - Sorry)

Note: Sorry for cross posting this question with
microsoft.public.sqlserver.programming, but I actually only intended to post
this question here...
A: I can create an XML fragment using the For XML Auto clause. No problem
so far.
B: I have a stored procedure that takes an XML document as ntext and uses
the extended sproc sp_xml_preparedocument and the OPENXML function to create
a table from it - no problem with that.
Now I want to execute my stored procedure using the XML result of part A as
the parameter for the sproc in part B. Is that possible? The only way I
know how to do it right now is to return the result of A using ADO.NET,
insert a root element (to make the XML valid), and then call the sproc
created in part B with this xml as the parameter.Hi Dave,
Welcome to MSDN newsgroup.
Regarding on this issue, I've also noticed your another duplicated thread
in the
Newsgroups: microsoft.public.sqlserver.programming
One community member Kent have posted some good suggestion there. Please
have a look and feel free to post in that thread...
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Dave Slinn" <CougarDave@.noemail.noemail>
| Subject: Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post
- Sorry)
| Date: Wed, 7 Dec 2005 12:36:59 -0600
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#Rkm201#FHA.3804@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.xml
| NNTP-Posting-Host: online.gms.ca 142.165.52.112
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.xml:26733
| X-Tomcat-NG: microsoft.public.sqlserver.xml
|
| Note: Sorry for cross posting this question with
| microsoft.public.sqlserver.programming, but I actually only intended to
post
| this question here...
|
| A: I can create an XML fragment using the For XML Auto clause. No problem
| so far.
|
| B: I have a stored procedure that takes an XML document as ntext and uses
| the extended sproc sp_xml_preparedocument and the OPENXML function to
create
| a table from it - no problem with that.
|
| Now I want to execute my stored procedure using the XML result of part A
as
| the parameter for the sproc in part B. Is that possible? The only way I
| know how to do it right now is to return the result of A using ADO.NET,
| insert a root element (to make the XML valid), and then call the sproc
| created in part B with this xml as the parameter.
|
|
||||In SQL Server 2005, you can utilize the XML datatype to do this very easily.
In SQL Server 2000, FOR XML goes always back to the client only, so using
ADO/OLEDB/ADO.Net in between is necessary...
Best regards
Michael
"Dave Slinn" <CougarDave@.noemail.noemail> wrote in message
news:%23Rkm201%23FHA.3804@.TK2MSFTNGP14.phx.gbl...
> Note: Sorry for cross posting this question with
> microsoft.public.sqlserver.programming, but I actually only intended to
> post this question here...
> A: I can create an XML fragment using the For XML Auto clause. No problem
> so far.
> B: I have a stored procedure that takes an XML document as ntext and uses
> the extended sproc sp_xml_preparedocument and the OPENXML function to
> create
> a table from it - no problem with that.
> Now I want to execute my stored procedure using the XML result of part A
> as
> the parameter for the sproc in part B. Is that possible? The only way I
> know how to do it right now is to return the result of A using ADO.NET,
> insert a root element (to make the XML valid), and then call the sproc
> created in part B with this xml as the parameter.
>

Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post - Sorry)

Note: Sorry for cross posting this question with
microsoft.public.sqlserver.programming, but I actually only intended to post
this question here...
A: I can create an XML fragment using the For XML Auto clause. No problem
so far.
B: I have a stored procedure that takes an XML document as ntext and uses
the extended sproc sp_xml_preparedocument and the OPENXML function to create
a table from it - no problem with that.
Now I want to execute my stored procedure using the XML result of part A as
the parameter for the sproc in part B. Is that possible? The only way I
know how to do it right now is to return the result of A using ADO.NET,
insert a root element (to make the XML valid), and then call the sproc
created in part B with this xml as the parameter.
Hi Dave,
Welcome to MSDN newsgroup.
Regarding on this issue, I've also noticed your another duplicated thread
in the
Newsgroups: microsoft.public.sqlserver.programming
One community member Kent have posted some good suggestion there. Please
have a look and feel free to post in that thread...
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Dave Slinn" <CougarDave@.noemail.noemail>
| Subject: Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post
- Sorry)
| Date: Wed, 7 Dec 2005 12:36:59 -0600
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#Rkm201#FHA.3804@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.xml
| NNTP-Posting-Host: online.gms.ca 142.165.52.112
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP14.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.xml:26733
| X-Tomcat-NG: microsoft.public.sqlserver.xml
|
| Note: Sorry for cross posting this question with
| microsoft.public.sqlserver.programming, but I actually only intended to
post
| this question here...
|
| A: I can create an XML fragment using the For XML Auto clause. No problem
| so far.
|
| B: I have a stored procedure that takes an XML document as ntext and uses
| the extended sproc sp_xml_preparedocument and the OPENXML function to
create
| a table from it - no problem with that.
|
| Now I want to execute my stored procedure using the XML result of part A
as
| the parameter for the sproc in part B. Is that possible? The only way I
| know how to do it right now is to return the result of A using ADO.NET,
| insert a root element (to make the XML valid), and then call the sproc
| created in part B with this xml as the parameter.
|
|
|
|||In SQL Server 2005, you can utilize the XML datatype to do this very easily.
In SQL Server 2000, FOR XML goes always back to the client only, so using
ADO/OLEDB/ADO.Net in between is necessary...
Best regards
Michael
"Dave Slinn" <CougarDave@.noemail.noemail> wrote in message
news:%23Rkm201%23FHA.3804@.TK2MSFTNGP14.phx.gbl...
> Note: Sorry for cross posting this question with
> microsoft.public.sqlserver.programming, but I actually only intended to
> post this question here...
> A: I can create an XML fragment using the For XML Auto clause. No problem
> so far.
> B: I have a stored procedure that takes an XML document as ntext and uses
> the extended sproc sp_xml_preparedocument and the OPENXML function to
> create
> a table from it - no problem with that.
> Now I want to execute my stored procedure using the XML result of part A
> as
> the parameter for the sproc in part B. Is that possible? The only way I
> know how to do it right now is to return the result of A using ADO.NET,
> insert a root element (to make the XML valid), and then call the sproc
> created in part B with this xml as the parameter.
>