|
|
|
|
|
How can I pass an array of
values to a SQL Server stored-procedure. Basically you can't - SQL Server has no array type - ANSI SQL 92 does not specify array support. But there are various ways around it. 1. You could simulate an array by passing one or more varchar(255) fields with comma-separated values and then use a WHILE loop with PATINDEX and SUBSTR to extract the values. 2. The more usual way to do this would be to populate a temporary table with the values you need and then use the contents of that table from within the stored-procedure. Example of this below create procedure mytest @MyParmTempTable varchar(30) as begin -- @MyParmTempTable contains my parameter list... -- For simplicity use dynamic sql to copy into a normal temp table... create table #MyInternalList ( list_item varchar( 2 ) not null) set nocount on insert #MyInternalList exec ( "select * from " + @MyParmTempTable ) set nocount off -- It is now easier to join.. select * from sysobjects where type in ( select list_item from #MyInternalList ) 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 "#MyList" 3. If all you wanted to do was use the array/list as input to an IN clause in a WHERE statement you could use :- REATE PROCEDURE sp_MyProcedure (@MyCommaDelimitedString Varchar(255)) AS BEGIN EXEC ('SELECT * FROM MYTABLE WHERE MYFIELD IN (' + @MyCommaDelimitedString + ')') END GO |
All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.