Passing Array with ids to stored procedure

I want to pass and array of ids to a procedure for inserting i a relation table.

I found some examples in other posts, but had problems getting them to work.

I just want to pass a parameter with value like '1,45,89' to the procedure, then loop through it to insert the relations.

(I´m using sql server 2000), had some problem with examples with strpos then.

Any hints ?

peace.

Create procedure ParseArray
( @.Array varchar(1000),
@.separator char(1) )
AS
-- Created by graz@.sqlteam.com
set nocount on
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
declare @.separator_position int -- This is used to locate each separator character
declare @.array_value varchar(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
while patindex('%' + @.separator + '%' , @.array) <> 0
begin
 -- patindex matches the a pattern against a string
 select @.separator_position = patindex('%' + @.separator + '%' , @.array)
 select @.array_value = left(@.array, @.separator_position - 1)
 -- This is where you process the values passed.
 -- Replace this select statement with your processing
 -- @.array_value holds the value of this element of the array
 select Array_Value = @.array_value
 -- This replaces what we just processed with and empty string
 select @.array = stuff(@.array, 1, @.separator_position, '')
end
set nocount off
go

enough documentation to xplain whats going on...

hth|||Don't bother using arrays (well until Yukon) this is one area where passing XML is helpful. Construct a simple XML string <r><i x="10" /><i x="10"/></r> etc and pass that as text to the proc (if you're client object supports serialization the xml might already be there). In the proc convert the XML into a table var. Then off you go, the array is now a cell per row.

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.

passing array data types from/to a stored procedure?

Is there any way passing array data types from/to a stored procedure?
Please reply. Thanks in advance.
Regards,
Hyun-jik BaeBae
SQL Server does not supprt arrays but you can do something like that
CREATE PROCEDURE array_sp
@.array nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nsql nvarchar(4000)
SET @.nsql = '
SELECT *
FROM sysobjects
WHERE name IN ( ' + @.array + ')'
PRINT @.nsql
EXEC sp_executesql @.nsql
END
GO
EXEC array_sp
@.array = '''sysobjects'',''sysindexes'',''syscolu
mns'''
GO
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> wrote in message
news:%23pxpkj8TFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Is there any way passing array data types from/to a stored procedure?
> Please reply. Thanks in advance.
> Regards,
> Hyun-jik Bae
>|||Passing Arrays:
http://vyaskn.tripod.com/passing_ar..._procedures.htm
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:uqnPwu8TFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Bae
> SQL Server does not supprt arrays but you can do something like that
> CREATE PROCEDURE array_sp
> @.array nvarchar(4000)
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.nsql nvarchar(4000)
> SET @.nsql = '
> SELECT *
> FROM sysobjects
> WHERE name IN ( ' + @.array + ')'
> PRINT @.nsql
> EXEC sp_executesql @.nsql
> END
> GO
>
> EXEC array_sp
> @.array = '''sysobjects'',''sysindexes'',''syscolu
mns'''
> GO
> "Bae,Hyun-jik" <imays@.NOSPAM.paran.com> wrote in message
> news:%23pxpkj8TFHA.1796@.TK2MSFTNGP15.phx.gbl...
>|||http://www.sommarskog.se/arrays-in-sql.html
Jacco Schalkwijk
SQL Server MVP
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> wrote in message
news:%23pxpkj8TFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Is there any way passing array data types from/to a stored procedure?
> Please reply. Thanks in advance.
> Regards,
> Hyun-jik Bae
>|||SQL has one data structure, the table. There are not arrays. You can
kludge it with strings that hold CSV. It is slow, procedural and
cannot guarantee data integrity. The right way is to load the data into
a table and start thinking in terms of SQL solutions instead of your
previous programming languages.

Passing an indeterminate number of parameters

Hi all,
Does anyone know if it's possible to somehow pass an unknown number of
parameters to a storerd procedure.
For example via an array of some description? Alternatively, in C# you can
use the keyword "params" when the number of parameters to be passed is
unknown at design time. I don't think there is any equivalent in SQL server
though is there?
Thanks everyone
Simon
Array datatype is not supported in SQL Server. However you can use certain
workarounds for such requirements, some of which are detailed at:
http://www.sommarskog.se/arrays-in-sql.html
Anith
|||Simon
As far as I know there is not a way to do what you ask. What I feel you will have to do is program your proc for the most parameters possible and give them default values. That way if you only pass a few then your proc knows what to do with the remainin
g values. You can easily build the string you pass to the proc in your code.
Hope this helps
Jeff Duncan
MCDBA, MCSE+I
|||Thanks guys. Both very useful answers
Kindest regards
Simon

Passing an indeterminate number of parameters

Hi all,
Does anyone know if it's possible to somehow pass an unknown number of
parameters to a storerd procedure.
For example via an array of some description? Alternatively, in C# you can
use the keyword "params" when the number of parameters to be passed is
unknown at design time. I don't think there is any equivalent in SQL server
though is there?
Thanks everyone
SimonArray datatype is not supported in SQL Server. However you can use certain
workarounds for such requirements, some of which are detailed at:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||Simon
As far as I know there is not a way to do what you ask. What I feel you wil
l have to do is program your proc for the most parameters possible and give
them default values. That way if you only pass a few then your proc knows w
hat to do with the remainin
g values. You can easily build the string you pass to the proc in your code
.
Hope this helps
Jeff Duncan
MCDBA, MCSE+I|||Thanks guys. Both very useful answers
Kindest regards
Simon

Passing an indeterminate number of parameters

Hi all,
Does anyone know if it's possible to somehow pass an unknown number of
parameters to a storerd procedure.
For example via an array of some description? Alternatively, in C# you can
use the keyword "params" when the number of parameters to be passed is
unknown at design time. I don't think there is any equivalent in SQL server
though is there?
Thanks everyone
SimonArray datatype is not supported in SQL Server. However you can use certain
workarounds for such requirements, some of which are detailed at:
http://www.sommarskog.se/arrays-in-sql.html
--
Anith|||Simo
As far as I know there is not a way to do what you ask. What I feel you will have to do is program your proc for the most parameters possible and give them default values. That way if you only pass a few then your proc knows what to do with the remaining values. You can easily build the string you pass to the proc in your code
Hope this help
Jeff Dunca
MCDBA, MCSE+I|||Thanks guys. Both very useful answers
Kindest regards
Simon

Passing an array to sql

I haven't done sql in a year so I could use some help here. I've got a
procedure below that uses the Function (listed under it here) that is
supposed to parse a string and pass the parsed string as an array to sql.
I'm not doing something correctly. If I pass in a single Symbol (string) my
procedure returns what it is supposed to, but if I pass in a string like thi
s
'A,B,C' nothing is returned, as though there is no parsing taking place.
The function (http://www.sommarskog.se/arrays-in-sql.html#iterative) also
works when I run the example, so there must be some mistake in the way I've
writtem my procedure. I think the line in question is my last 'Join'
statement.
Anyone have any ideas?
Thanks,
Paul
===========
--- My Procedure
--
ALTER PROCEDURE [dbo].[_Portfolios_Basic] (@.PortfolioSymbols NvarChar(max))
AS
SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Sector.Sector,
a_Industry.Industry, a_Quarter_Index.Period, a_Financials.[00_Sales] AS
Revenue,
a_Financials.[15_Net_Inc_from_con_ops] AS Income,
a_Financials.[26_EPS_from_con_ops] AS EPS,
a_Financials.[15_Margins_-_NET_con_ops] AS [Net
Margin], a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo,
a_Hyperlinks.MSN_10Qs AS Financials,
a_Hyperlinks.MSN_events AS Events, a_Hyperlinks.StockCharts AS TA1
FROM a_Hyperlinks
INNER JOIN
a_Financials ON a_Hyperlinks.Yahoo_Main =
a_Financials.Yahoo_Main
INNER JOIN
a_Industry ON a_Financials.Industry = a_Industry.Industry
INNER JOIN
a_Sector ON a_Financials.Sector = a_Sector.Sector
INNER JOIN
a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period
INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
JOIN
iter_charlist_to_table(@.PortfolioSymbols
, DEFAULT) s ON
a_Name_Symbol.Symbol = s.nstr
WHERE (a_Name_Symbol.Symbol IN (@.PortfolioSymbols))
ORDER BY a_Name_Symbol.Name
--- iter_charlist_to_table
Function --
List-of-strings
Here is a similar function, but that returns a table of strings.
CREATE FUNCTION iter_charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
Here is an example on how you would use the function:
CREATE PROCEDURE get_company_names_iter @.customers nvarchar(2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Customers C
JOIN iter_charlist_to_table(@.customers, DEFAULT) s ON C.CustomerID
= s.nstr
go
EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'I was given the answer.
I just needed to remove the IN function in the WHERE clause.
========================================
=====
"a" wrote:

> I haven't done sql in a year so I could use some help here. I've got a
> procedure below that uses the Function (listed under it here) that is
> supposed to parse a string and pass the parsed string as an array to sql.
> I'm not doing something correctly. If I pass in a single Symbol (string)
my
> procedure returns what it is supposed to, but if I pass in a string like t
his
> 'A,B,C' nothing is returned, as though there is no parsing taking place.
> The function (http://www.sommarskog.se/arrays-in-sql.html#iterative) also
> works when I run the example, so there must be some mistake in the way I'v
e
> writtem my procedure. I think the line in question is my last 'Join'
> statement.
> Anyone have any ideas?
> Thanks,
> Paul
> ===========
>
> --- My Procedur
e --
> ALTER PROCEDURE [dbo].[_Portfolios_Basic] (@.PortfolioSymbols NvarChar(max))
> AS
> SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Sector.Sector,
> a_Industry.Industry, a_Quarter_Index.Period, a_Financials.[00_Sales] AS
> Revenue,
> a_Financials.[15_Net_Inc_from_con_ops] AS Income,
> a_Financials.[26_EPS_from_con_ops] AS EPS,
> a_Financials.[15_Margins_-_NET_con_ops] AS [Net
> Margin], a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo,
> a_Hyperlinks.MSN_10Qs AS Financials,
> a_Hyperlinks.MSN_events AS Events, a_Hyperlinks.StockCharts AS TA1
> FROM a_Hyperlinks
> INNER JOIN
> a_Financials ON a_Hyperlinks.Yahoo_Main =
> a_Financials.Yahoo_Main
> INNER JOIN
> a_Industry ON a_Financials.Industry = a_Industry.Industry
> INNER JOIN
> a_Sector ON a_Financials.Sector = a_Sector.Sector
> INNER JOIN
> a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Peri
od
> INNER JOIN
> a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
> JOIN
> iter_charlist_to_table(@.PortfolioSymbols
, DEFAULT) s ON
> a_Name_Symbol.Symbol = s.nstr
> WHERE (a_Name_Symbol.Symbol IN (@.PortfolioSymbols))
> ORDER BY a_Name_Symbol.Name
>
> --- iter_charlist_to_table
> Function --
> List-of-strings
> Here is a similar function, but that returns a table of strings.
> CREATE FUNCTION iter_charlist_to_table
> (@.list ntext,
> @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END
> Here is an example on how you would use the function:
> CREATE PROCEDURE get_company_names_iter @.customers nvarchar(2000) AS
> SELECT C.CustomerID, C.CompanyName
> FROM Customers C
> JOIN iter_charlist_to_table(@.customers, DEFAULT) s ON C.CustomerI
D
> = s.nstr
> go
> EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'
>|||> writtem my procedure. I think the line in question is my last 'Join'
> statement.
I suspect you problem is the WHERE clause:

> WHERE (a_Name_Symbol.Symbol IN (@.PortfolioSymbols))
It looks to me that this an artifact of a previous incorrect technique and
is superseded by your join to the table-valued function. Also, it looks
like you are using SQL 2005 since I see varchar(MAX). In that case, you
might consider passing XML. Untested example:
ALTER PROCEDURE [dbo].[_Portfolios_Basic] (@.PortfolioSymbols xml)
AS
SELECT
a_Name_Symbol.Name,
a_Name_Symbol.Symbol,
a_Sector.Sector,
a_Industry.Industry,
a_Quarter_Index.Period,
a_Financials.[00_Sales] AS Revenue,
a_Financials.[15_Net_Inc_from_con_ops] AS Income,
a_Financials.[26_EPS_from_con_ops] AS EPS,
a_Financials.[15_Margins_-_NET_con_ops] AS [Net Margin],
a_Financials.PE,
a_Hyperlinks.Yahoo_Main AS Yahoo,
a_Hyperlinks.MSN_10Qs AS Financials,
a_Hyperlinks.MSN_events AS Events,
a_Hyperlinks.StockCharts AS TA1
FROM a_Hyperlinks
JOIN a_Financials ON
a_Hyperlinks.Yahoo_Main = a_Financials.Yahoo_Main
JOIN a_Industry ON
a_Financials.Industry = a_Industry.Industry
JOIN a_Sector ON
a_Financials.Sector = a_Sector.Sector
JOIN a_Quarter_Index ON
a_Financials.Period = a_Quarter_Index.Period
JOIN a_Name_Symbol ON
a_Financials.Symbol = a_Name_Symbol.Symbol
JOIN (SELECT CAST(PortfolioSymbols.PortfolioSymbol.query('.') AS char(5)) AS
PortfolioSymbol
FROM @.PortfolioSymbols.nodes('/PortfolioSymbols/PortfolioSymbol/text()')
PortfolioSymbols(PortfolioSymbol)) AS PortfolioSymbols ON
a_Name_Symbol.Symbol = PortfolioSymbols.PortfolioSymbol
ORDER BY a_Name_Symbol.Name
GO
EXEC get_company_names_iter '<PortfolioSymbols>
<PortfolioSymbol>ALFKI</PortfolioSymbol>
<PortfolioSymbol>BONAP</PortfolioSymbol>
<PortfolioSymbol>CACTU</PortfolioSymbol>
<PortfolioSymbol>FRANK</PortfolioSymbol>
</PortfolioSymbols>'
Hope this helps.
Dan Guzman
SQL Server MVP
"a" <a@.discussions.microsoft.com> wrote in message
news:B6AF913C-74FE-4214-BBA1-768ED66ADD67@.microsoft.com...
>I haven't done sql in a year so I could use some help here. I've got a
> procedure below that uses the Function (listed under it here) that is
> supposed to parse a string and pass the parsed string as an array to sql.
> I'm not doing something correctly. If I pass in a single Symbol (string)
> my
> procedure returns what it is supposed to, but if I pass in a string like
> this
> 'A,B,C' nothing is returned, as though there is no parsing taking place.
> The function (http://www.sommarskog.se/arrays-in-sql.html#iterative) also
> works when I run the example, so there must be some mistake in the way
> I've
> writtem my procedure. I think the line in question is my last 'Join'
> statement.
> Anyone have any ideas?
> Thanks,
> Paul
> ===========
>
> --- My
> Procedure --
> ALTER PROCEDURE [dbo].[_Portfolios_Basic] (@.PortfolioSymbols
> NvarChar(max))
> AS
> SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Sector.Sector,
> a_Industry.Industry, a_Quarter_Index.Period, a_Financials.[00_Sales] AS
> Revenue,
> a_Financials.[15_Net_Inc_from_con_ops] AS Income,
> a_Financials.[26_EPS_from_con_ops] AS EPS,
> a_Financials.[15_Margins_-_NET_con_ops] AS [Net
> Margin], a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo,
> a_Hyperlinks.MSN_10Qs AS Financials,
> a_Hyperlinks.MSN_events AS Events, a_Hyperlinks.StockCharts AS TA1
> FROM a_Hyperlinks
> INNER JOIN
> a_Financials ON a_Hyperlinks.Yahoo_Main =
> a_Financials.Yahoo_Main
> INNER JOIN
> a_Industry ON a_Financials.Industry = a_Industry.Industry
> INNER JOIN
> a_Sector ON a_Financials.Sector = a_Sector.Sector
> INNER JOIN
> a_Quarter_Index ON a_Financials.Period =
> a_Quarter_Index.Period
> INNER JOIN
> a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
> JOIN
> iter_charlist_to_table(@.PortfolioSymbols
, DEFAULT) s ON
> a_Name_Symbol.Symbol = s.nstr
> WHERE (a_Name_Symbol.Symbol IN (@.PortfolioSymbols))
> ORDER BY a_Name_Symbol.Name
>
> --- iter_charlist_to_table
> Function --
> List-of-strings
> Here is a similar function, but that returns a table of strings.
> CREATE FUNCTION iter_charlist_to_table
> (@.list ntext,
> @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END
> Here is an example on how you would use the function:
> CREATE PROCEDURE get_company_names_iter @.customers nvarchar(2000) AS
> SELECT C.CustomerID, C.CompanyName
> FROM Customers C
> JOIN iter_charlist_to_table(@.customers, DEFAULT) s ON C.CustomerID
> = s.nstr
> go
> EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'
>

Passing an array to query in a table adapter

I'm looking for a way to pass an array of values as a parameter to a query in a table adapter. For example I want to run a query something like:

SELECT * FROM menu WHERE menu_role IN (@.roles)

And I could pass something like 'RegisteredUser, SuperUser, OtherUser' to the @.roles parameter.

For some reason I can't figure out a way to do this. Any help would be greatly appericated.

Thanks,

Ryan.

This is possible from only code behind. Dynamically create the string and assrign it to the select command of tableadapter.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=319884&SiteID=1

check this link

|||Thanks, but I don't think that that helps my situation. I guess I will just have to create a query string on the fly even though I hate doing that.|||

Check if this helps:http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx

|||

There's more than one way to skin a cat...

I was trying to figure out the same thing when I realized I could filter the data AFTER it was returned. I put it in a dataview and used the rowfilter property. Hope that helps!

|||My solutions was to create a stored procedure that dynamically setup the query on the fly before sending the results back. All in all it turned out to be a fairly elegant solution, although I do wish I could just pass an array in through a parameter.

Passing an Array to a Stored Procedures

How to do this ?

==============================
CREATE procedure dbo.AddTb2FromTb1
@.Tb1No nvarchar(1000)
as
insert into Tb2 (*)
select * from Tb1
where Tb1 IN (@.Tb1No) /* How to Passing an Array to a Stored Procedures ? */
==============================

dbo.AddTb2FromTb1 'No001' is Work !
dbo.AddTb2FromTb1 'No001,No002,Bo003' is not Work !If you are using SQL 2000 you can create a user-defined fnction - have a look at the following link:

Sql Team|||I Try CnvToChar() is work...

select * from CsvToChar('PO001234,PO123') is work

CharValue
-----
PO001234
Po123
-----

but,

=====================================================
select * from Tb1 Where TbNo IN (select * from CsvToChar('PO001234,PO123'))
have error message:

Server: Message 446, Level 16, Status 9, Line 1
can not to analyze 'equal to' action order collide.
=====================================================

thank you help...|||My SQL Server is Chinese System,
Character Record Fields in Table is default 'COLLATE Chinese_Taiwan_Stroke_BIN' vaule,
so i add the Default value in the function CvsTochar() return value.

==============================================
CREATE Function dbo.CsvToChar ( @.Array varchar(1000))
returns @.CharTable table
(CharValue char(10) COLLATE Chinese_Taiwan_Stroke_BIN)
AS
begin

declare @.separator char(1)
set @.separator = ','
.........
.........
===============================================

No error message to display, It's work... ^_^ Y

Ehorn - Thank you very much !!!

Johnny SCB

Passing an array of values for a single parameter

I am using a reportviewer control on my web form and I have created a parameter in my report. I can pass a hardcoded parameter and it works for one values at a time, but I want to send an array of values for the same parameter. For example if I have 5 different fleets of aircaft I might want to see 1,2 or all of the fleet in this report. I am kind of new to SQL reporting any help would be great.

Thanks in advance

you can pass an array but you need to do some processing in the RS designer..under the Data tab. or you can do it all in the stored proc it self by setting up the parameter as varchar and doing the splitting inside..|||

Thanks I will look into RS Designer Data Tab. Is there some sample code available the performs this particular function.

|||check out my blog..there is an article about parsing an array..there is also a link to a better article..it involves using UDFs. It is prbly a better and more efficient approach from the stored proc itself.|||

Could I use a parameter collection to pass an Array of Parameters to a report, if so how would I code that?

Thanks

|||prios did you get this working? If so, could you post your solution? I would also be interested in seeing how you passed a single parameter using the report viewer.
Thanks.|||

I found a solution...

http://odetocode.com/Articles/128.aspx

Passing an array of strings to a Stored Procedure

Well, Imanaged to write a Stored procedure that updates some records in the Northwind Database based on the ProductIDs passed to the SP as a list of strings. This is the Alter version of the SP:

USE [Northwind]

GO

/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

ALTERPROC [dbo].[gv_sp_UpdatePOs]

(

@.IDListvarchar(500),

@.ReorderLevelint,

@.ProductNamenvarchar(30)

)

AS

BEGIN

SETNOCOUNTON

EXEC('Update dbo.Products

SET ReorderLevel = ('+ @.ReorderLevel+') ,ProductName = ('''+ @.ProductName+''')

WHERE ProductID IN ('+ @.IDList+')')

END

-------

THis works fine inside Sql Server 2005 Query analyser.

But when I setup an aspx page with an objectDataSource inside the page attached to an xsd file where the Products table is located. When I try to add new query to the tableadapter inside the Products table and point to the stored procedure in the wizard I get this error: " the wizard detected the following problems when configuring TableAdapter query "Products" Details: Generated SELECT statement. Incorrect suntax near ')'.

Any help would be appreciated

And can someone convert it to support XML instead of list of strings. thanks.

Hello my friend,

It would be better to do the following. First, run the following SQL: -

CREATE FUNCTION dbo.StringArrayIntoTable
(
@.String VARCHAR(8000),
@.Separator VARCHAR(1)
)
RETURNS @.tblStrings TABLE(Item VARCHAR(8000))

AS

BEGIN

DECLARE @.pos INT,
@.SubStr VARCHAR(800)

SET @.pos = CHARINDEX(@.Separator, @.String)

WHILE @.pos > 0
BEGIN
SET @.SubStr = SUBSTRING(@.String, 0, @.pos)

INSERT INTO @.tblStrings (Item) VALUES (@.SubStr)

SET @.String = SUBSTRING(@.String, LEN(@.SubStr) + 2, LEN(@.String) - LEN(@.SubStr) + 1)
SET @.pos = CHARINDEX(@.Separator, @.String)
END

INSERT INTO @.tblStrings (Item) VALUES (@.String)
RETURN

END

Test this function via the following: -

SELECT Item FROM dbo.StringArrayIntoTable('red,blue,yellow', ',')
SELECT Item FROM dbo.StringArrayIntoTable('USA|Germany|Russia|UK', '|')

Now change your procedure to the following: -

ALTER PROC [dbo].[gv_sp_UpdatePOs]
(
@.IDList varchar(500),
@.ReorderLevel int,
@.ProductName nvarchar(30)
)
AS

BEGIN

SET NOCOUNT ON

UPDATE dbo.Products SET
ReorderLevel = @.ReorderLevel,
ProductName = @.ProductName

WHERE ProductID IN
(
SELECT Item FROM dbo.StringArrayIntoTable(@.IDList, ',')
)

END

Kind regards

Scotty

|||

USE [Northwind]GO/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOALTER PROC [dbo].[gv_sp_UpdatePOs](@.IDListvarchar(500),@.ReorderLevelint,@.ProductNamenvarchar(30) )ASBEGINSET NOCOUNT ON EXEC('Update dbo.ProductsSET ReorderLevel = (' +CAST( @.ReorderLevelas varchar(20) ) +') ,ProductName = (''' + @.ProductName +''')WHERE ProductID IN (' + @.IDList +')')END

Hello,
Try this.|||

Scotty, nice trick thanks.

Hasan, thanks. It is working now with casting.

passing an array into a stored procedure

I am trying to pass a set of id values into a stored procedure.
Currently i am comma seperating these into a varchar to achieve this.
the statement is then executed as follows:
exec 'select * from table where ID in (' + @.VarCharParam + ') Order By
ID'
This dows work fine, but there must be a better way.
Any help would be appreciated
Regards
Grant Merwitz
Hi Grant
The approach you've taken certainly does have it's problems, not the least
of which is that it's subject to SQL injection if it's accessible outside
the DB. Do make sure you understand SQL injection as a minimum before
rolling code like that out.
However, TSQL doesn't have arrays. A common approach to this problem is to
pass in xml either in varchar or text variables which can be opened inside
the stored proc using the sp_xml_preparedocument system proc.
Otherwise, if you're confident you're not subject to injection & you know
you'll only pass in a short list of variables, the approach you've used does
have some merit in that it's light-weight & doesn't varry the overhead of a
few of it's alternatives.
HTH
Regards,
Greg Linwood
SQL Server MVP
"GrantMagic" <grant@.magicalia.com> wrote in message
news:%23PRCD1%23bEHA.3580@.TK2MSFTNGP11.phx.gbl...
> I am trying to pass a set of id values into a stored procedure.
> Currently i am comma seperating these into a varchar to achieve this.
> the statement is then executed as follows:
> exec 'select * from table where ID in (' + @.VarCharParam + ') Order By
> ID'
> This dows work fine, but there must be a better way.
> Any help would be appreciated
> Regards
> Grant Merwitz
>
|||> the statement is then executed as follows:
> exec 'select * from table where ID in (' + @.VarCharParam + ') Order By
> ID'
> This dows work fine, but there must be a better way.
SQL Server doesn't know what an array is. See http://www.aspfaq.com/2248
for an alternative approch, and the links therein for more information.
http://www.aspfaq.com/
(Reverse address to reply.)
|||SQL Server may not know what arrays are, but Erland Sommerskog does :-)
http://www.sommarskog.se/arrays-in-sql.html
It never hurts to set up a table of integers, with a clustered unique index.
One thing SQL Server DOES know how to do is iterate fast through
a set of rows.
You might want to consider the 'monster parameter list' approach.
It works if you can put a reasonable bound (under 1024) on the number
of array elements.
It causes you to generate a lot of repetitive SQL, but once the sproc's
query plan
has been generated, the resulting interpreted code is fast.
CREATE PROC DoThat
@.This varchar(99), @.That varchar(99)
,@.A00 INT=NULL, @.A01 INT=NULL, @.A02 INT=NULL, ...
,@.A10 INT=NULL, @.A11 INT=NULL, @.A12 INT=NULL, ...
...
AS
DECLARE @.A TABLE(val int)
INSERT @.A SELECT *
FROM ( SELECT @.A00 val
UNION ALL SELECT @.A01
UNION ALL SELECT @.A02
...
) X
WHERE val IS NOT NULL
... go wild
If you can't use a default marker like NULL, you need a slightly different
approach:
CREATE PROC DoThat
@.This varchar(99), @.That varchar(99), @.ArgCount INT
,@.A00 INT=NULL, @.A01 INT=NULL, @.A02 INT=NULL, ...
,@.A10 INT=NULL, @.A11 INT=NULL, @.A12 INT=NULL, ...
...
AS
DECLARE @.A TABLE(val int)
INSERT @.A SELECT val
FROM ( SELECT @.A00 val, 00 AS seq
UNION ALL SELECT @.A01, 01
UNION ALL SELECT @.A02, 02
...
) X
WHERE seq < @.ArgCount
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5XVhD$bEHA.2972@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
By
> SQL Server doesn't know what an array is. See http://www.aspfaq.com/2248
> for an alternative approch, and the links therein for more information.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>

passing an array into a stored procedure

I am trying to pass a set of id values into a stored procedure.
Currently i am comma seperating these into a varchar to achieve this.
the statement is then executed as follows:
exec 'select * from table where ID in (' + @.VarCharParam + ') Order By
ID'
This dows work fine, but there must be a better way.
Any help would be appreciated
Regards
Grant MerwitzHi Grant
The approach you've taken certainly does have it's problems, not the least
of which is that it's subject to SQL injection if it's accessible outside
the DB. Do make sure you understand SQL injection as a minimum before
rolling code like that out.
However, TSQL doesn't have arrays. A common approach to this problem is to
pass in xml either in varchar or text variables which can be opened inside
the stored proc using the sp_xml_preparedocument system proc.
Otherwise, if you're confident you're not subject to injection & you know
you'll only pass in a short list of variables, the approach you've used does
have some merit in that it's light-weight & doesn't varry the overhead of a
few of it's alternatives.
HTH
Regards,
Greg Linwood
SQL Server MVP
"GrantMagic" <grant@.magicalia.com> wrote in message
news:%23PRCD1%23bEHA.3580@.TK2MSFTNGP11.phx.gbl...
> I am trying to pass a set of id values into a stored procedure.
> Currently i am comma seperating these into a varchar to achieve this.
> the statement is then executed as follows:
> exec 'select * from table where ID in (' + @.VarCharParam + ') Order By
> ID'
> This dows work fine, but there must be a better way.
> Any help would be appreciated
> Regards
> Grant Merwitz
>|||> the statement is then executed as follows:
> exec 'select * from table where ID in (' + @.VarCharParam + ') Order By
> ID'
> This dows work fine, but there must be a better way.
SQL Server doesn't know what an array is. See http://www.aspfaq.com/2248
for an alternative approch, and the links therein for more information.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||SQL Server may not know what arrays are, but Erland Sommerskog does :-)
http://www.sommarskog.se/arrays-in-sql.html
It never hurts to set up a table of integers, with a clustered unique index.
One thing SQL Server DOES know how to do is iterate fast through
a set of rows.
You might want to consider the 'monster parameter list' approach.
It works if you can put a reasonable bound (under 1024) on the number
of array elements.
It causes you to generate a lot of repetitive SQL, but once the sproc's
query plan
has been generated, the resulting interpreted code is fast.
CREATE PROC DoThat
@.This varchar(99), @.That varchar(99)
,@.A00 INT=NULL, @.A01 INT=NULL, @.A02 INT=NULL, ...
,@.A10 INT=NULL, @.A11 INT=NULL, @.A12 INT=NULL, ...
...
AS
DECLARE @.A TABLE(val int)
INSERT @.A SELECT *
FROM ( SELECT @.A00 val
UNION ALL SELECT @.A01
UNION ALL SELECT @.A02
...
) X
WHERE val IS NOT NULL
... go wild
If you can't use a default marker like NULL, you need a slightly different
approach:
CREATE PROC DoThat
@.This varchar(99), @.That varchar(99), @.ArgCount INT
,@.A00 INT=NULL, @.A01 INT=NULL, @.A02 INT=NULL, ...
,@.A10 INT=NULL, @.A11 INT=NULL, @.A12 INT=NULL, ...
...
AS
DECLARE @.A TABLE(val int)
INSERT @.A SELECT val
FROM ( SELECT @.A00 val, 00 AS seq
UNION ALL SELECT @.A01, 01
UNION ALL SELECT @.A02, 02
...
) X
WHERE seq < @.ArgCount
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5XVhD$bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> > the statement is then executed as follows:
> > exec 'select * from table where ID in (' + @.VarCharParam + ') Order
By
> > ID'
> >
> > This dows work fine, but there must be a better way.
> SQL Server doesn't know what an array is. See http://www.aspfaq.com/2248
> for an alternative approch, and the links therein for more information.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>

Passing an Array and/or Variable Field Name to an SProc

I have 2 questions.

I am trying to write a stored procedure to update a table. I am trying
to pass a variable that represents the name of the column/field and
another for the value that I am changing.

For example:
@.FieldName VARCHAR(100)
@.FieldValue VARCHAR(100)
AS
UPDATE tblTHETABLE
SET @.FieldName = @.FieldValue

First is it possible to use a variable as the column/field name? If
so, how do I go about it?

Also, it would be nice if I could have the @.FieldName and @.FieldValue
variables as arrays. Is that possible?

Thank-you for any assistance
Bill"~TheIcemanCometh~" <bhazelwood@.delta-elevator.com> wrote in message
news:8d372e43.0402171320.5d263673@.posting.google.c om...
> I have 2 questions.
> I am trying to write a stored procedure to update a table. I am trying
> to pass a variable that represents the name of the column/field and
> another for the value that I am changing.
> For example:
> @.FieldName VARCHAR(100)
> @.FieldValue VARCHAR(100)
> AS
> UPDATE tblTHETABLE
> SET @.FieldName = @.FieldValue
> First is it possible to use a variable as the column/field name? If
> so, how do I go about it?
> Also, it would be nice if I could have the @.FieldName and @.FieldValue
> variables as arrays. Is that possible?
> Thank-you for any assistance
> Bill

The short answer is that it's possible, but probably not advisable. The
first link should help explain why; the second covers arrays:

http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/arrays-in-sql.html

Simon|||[posted and mailed, please reply in news]

~TheIcemanCometh~ (bhazelwood@.delta-elevator.com) writes:
> I am trying to write a stored procedure to update a table. I am trying
> to pass a variable that represents the name of the column/field and
> another for the value that I am changing.
> For example:
> @.FieldName VARCHAR(100)
> @.FieldValue VARCHAR(100)
> AS
> UPDATE tblTHETABLE
> SET @.FieldName = @.FieldValue
> First is it possible to use a variable as the column/field name? If
> so, how do I go about it?
> Also, it would be nice if I could have the @.FieldName and @.FieldValue
> variables as arrays. Is that possible?

Anything is possible, but what's the point? Why not construct the
SQL statements in client code instead?

If you really want to know how to do it, I have an article on my web
site. There you also learn why you should not do it.
http://www.sommarskog.se/dynamic_sql.html.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Passing an multi array to sql from java over jdbc

Hi Guys,

I am fairly new to PL and I am trying to solve this problem.

I need to pass a two dimensional array(A variable Name and Variable value) to a stored procedure in PL/SQL. I dont want to pass this data as a big string..I was hoping to passing it as an array because it would be easier to handle the data.

Basically I would be calling this procedure from a java program over JDBC.

My question is how do I create this...

Since I would be filling this from Java, it would be a parameter within the procedure something like

CREATE OR REPLACE PROCEDURE Test_Response ( pisa_response Varchar[][] )

Of course Varchar[][] is not supported by PL but just showed this to give more understanding of the problem

Any help on this would be really appreciated..

Thanks in advance..

Regards,
FreakieCheck the thread in the Java section of this "dbForums" for details.|||Hi Thanks 4 the reply...

From what I gathered.. I might need to send it as a string...but I do not want to do that...

I basically want to send it as an array of strings... I am not sure whether using ORacle.sql.array feature might help???

Regards,
Freakie|||Again, check the thread in the Java section of this forums. Its got
complete code to perform what your looking for, so long as your
running Oracle 9i.

Passing a table to a SP

I am making an SP that uses a table as an array. I want the user to pass th
e
table to the SP, where I will then iterate through it and pull out all of th
e
IDs and place them into a string that I can use with the IN keyword. Once I
get the table into the function, I will have no trouble. I just want to mak
e
sure that the table has at least a column of consecutive integers to use for
IDs while looping and another column of WorkIDs to be updated. I am making
this SP because it should be alot faster (I think) to use an IN statement
than having the SP update one record each time it is called and having to
call it many times. I figure that the client can just pass me a recordset
that they wish to be updated and I can pull the WorkIDs out of it.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsHi,
I'm not sure what the question is bu:
Assuming the table containing the request list is persistent (same name all
of the time):

code:

Update T1
Set T1.Col1 = @.SomeValue
From tbl_Target T1
Where Exists (Select * From tbl_List T2
Where T1.Id = T2.Id)

As long as you already have a table of keys, there is no need to manipulate
it into a string. If the table of keys also has a corresponding value to
assign, you can reference it instead of @.SomeValue and change the "Exists" t
o
a join. (Which you could do anyway here).
Good luck, and I hope I could help.
"Chris Lieb" wrote:

>
I am making an SP that uses a table as an array. I want the user to pass
the
>
table to the SP, where I will then iterate through it and pull out all of
the
>
IDs and place them into a string that I can use with the IN keyword. Once
I
>
get the table into the function, I will have no trouble. I just want to m
ake
>
sure that the table has at least a column of consecutive integers to use f
or
>
IDs while looping and another column of WorkIDs to be updated. I am makin
g
>
this SP because it should be alot faster (I think) to use an IN statement
>
than having the SP update one record each time it is called and having to
>
call it many times. I figure that the client can just pass me a recordset
>
that they wish to be updated and I can pull the WorkIDs out of it.
>
>
Thanks in advance
>
>
--
>
Chris Lieb
>
UPS CACH, Hodgekins, IL
>
Tech Support Group - Systems/Apps|||There are several approaches in t-SQL for such requirements. some of which
can be found at: http://www.sommarskog.se/arrays-in-sql.html
Anith

Passing a list/array to an SQL Server stored procedure 2005

Hi, I m using sql 2005 as a back end in my application...

I am useing Store procedure..for my data in grid..

ALTERPROCEDURE [dbo].[ProductZoneSearct]

(

@.Productidchar(8),
@.Pronamechar(8),
@.radiusint,
@.modevarchar(5)='M',
@.Zonenvarchar(1000),

)

AS
SETNOCOUNTON;
Create Table #Product (ProductID int, TimeEntered datetime, DateAvailable datetime, Productname varchar(80), City varchar(50), State char(4),Miles decimal, Payment varchar(40),UserID int, Phone varchar(15))

Insert #Product
Select ProductID , TimeEntered, DateAvailable, Productname ,City,State,miles,Payment
,Miles, UserID, Daily, Phone
From [tblproduct]
Where city IN (@.Zone)

Select ProductID TimeEntered, DateAvailable, Productname City,State,miles,Payment
,Miles, U.Phone As phoneNumber, Company, , L.Phone As cmpPhone
From #Product As L
Left Join (Select UserID, Company, Phone, From [User]) As U On U.UserID = L.UserID
Order By DateAvailable

if i pass value in"where city in (@.Zone)" and@.Zone ='CA','AD','MH' then it can not get any result..but if writewhere city in ('CA','AD','MH') then it give me perfact result..

I tried to below syntax also but in no any user
Where city IN ('+@.Zone+')

In short if i pass value through varibale (@.Zone) then i cant get result...but if i put direct value in query then only getting result..can anybody tell me what is problem ?

Please Hel[p me !!!

Thank you !!!

Check out this blog post:

Passing lists to SQL Server 2005 with XML Parameters

|||

Hmmm... you may be better off writing an object data source and filtering the data after the select. Otherwise, have a look at sp_executesql or somesuch...

|||

Problem is If i pass only one value into variable then it gives me result but if i pass more than one value then it wount give me result.
Example..If i pass @.Zone='KS' then it works fine but if i pass @.Zone='KS','MS' Then it wong give me data...coz "," (comma) .seperated..may be it count after comma seprateion its a new value...or i dont know why i m not getting result

Please help me

Thank you & Regards.

|||

Sorry I am not getting you..:-((

|||

Instead of passing the parameters to the stored procedure, you can just select everything, and filter the data after. Look at FilterParameters and FilterExpression for SQL DataSource. I think you can set the FilterExpression="WHERE Region IN (@.Region)", and add @.Region as a FilterParameter, or something like that. Otherwise, you can make an ObjectDataSource that executes the stored procedure, and you can filter the resultset inside there however you want.

EDIT

Well, the whole Filter parameter thing with an "IN" clause isn't working for me... however, I think you can handle the datasource's filtering event to build the filter expression.