Q. How can I Pass an Array of Values To a SQL Server Stored-Procedure.
(v1.1 1999.05.14)
A. Basical you can't - SQL Server Has No Array Type - ANSI SQL 92 Does Not Specify Array Support. But There Are Various Ways Around IT.
Exactly, the-SQL Server has no array type, and ANSI SQL 92 standard does not support arrays. However, it is possible to achieve other methods.
.
1. You can use several varchar (255) fields to simulate arrays, with commas in the field to separate each data, then separate this data using loops and PATINDEX and SUBSTR.
2. The More Usul Way to Do this Would Be To Populate a Temporary Table with the contents of this table, example of this best
2, usually this method needs to create a temporary table for these data, and then use the content in the stored procedure. Below
Create Procedure mytest @myparmtemptable varchar (30)
AS
Begin
- @Myparmtemptable Contains my parameter list ... This variable is a table name containing the parameters
- for Simplicity Use Dynamic SQL to Copy Into a Normal Temp Table ...
Create Table #myinternalNAllist (
List_item varchar (2) Not null
)
Set nocount on
INSERT #myinternalNAllist
Exec ("SELECT *" @myparmtemptable)
Set nocount off
- it is now easier to join ..
SELECT *
From sysobjects
WHERE TYPE IN (SELECT LIST_ITEM from #myinternal)
end
Go
To call ..
Create Table # MYLIST
List_item varchar (2) Not null
)
INSERT #MYLIST VALUES ('s')
INSERT #Mylist Values ('u')
INSERT #MYLIST VALUES ('P')
Exec mytest "# millet"
3. IF all you wanded to do WAS Use the array / list as infut to an in clause in a Where statement you could users: -
3, if you want to use the entered array parameters in the in clause, you can do this:
Create Procedure SP_MYPROCEDURE (@MYCOMMADELIMITEDSTRINGVARCHAR (255))
AS
Begin
Exec ('SELECT * from myTable where myfield in (' @mycommadelimitedstring ')')
End
Go
