Passing an array of integers to an SQL stored-procedure

[TonyKhela]

Is there a way to pass an array of integers to an SQL stored-procedure?

 

[foxdev]

No.

But to give a more complete answer, pass them to the SP from where? A client-side application or another SP? If from another SP, you might be able to pass a table type variable from Proc A to Proc B (table variables are always local, so I don't know if this would work). --------------
School supplies for the needy:

http://www.agrainofhope.org/classzoom.shtml

 

[clapag22]

We usually do this:

create proc proc_name @int_array varchar(255) as

declare @sql varchar(255)

select @sql = 'select f1, f2, etc
from tb_name
where f1 in (' + @int_array + ')
GO'

exec (@sql)
GO

so when executing the SP you would do:

exec proc_name '1, 4, 6, 55, 99'

 

[TonyKhela]

FoxDev, The type of solution you've suggested would be iideal.

However, the 'table' variable is OK as an OUTPUT parameter but seems NOT to be; as an INPUT parameter. Unless I've missed something, please clarify how this could be achieved.

Clapag22, the method you describe is the one we are using currently. Needed a neater solution, if possible.

Thanks for your help.

 

[clapag22]

If the table variable doesn't work for you and you don't like your current solution (which is what I suggested), the only other thing you could really do is run your SQL statement separately for each value in the array. You would need to do some string manipulation using PATINDEX.

I have this code that kind of does what you need to do:
---------
if @dsm4 is not null and @dsm4 != ''
begin
select @dx4_str = @dsm4

while patindex('%,%', @dx4_str) != 0
begin

insert into otr_link_axis_iv values (@otr_staging_id, left(@dx4_str, patindex('%,%', @dx4_str) - 1))

select @dx4_str = right(@dx4_str, len(@dx4_str) - patindex('%,%', @dx4_str))

end

insert into otr_link_axis_iv values (@otr_staging_id, @dx4_str)
end
---------

 

[SQLSister]

Get the book, The Guru's Guide to SQL Server Stored PRocedures, XML, and HTML by Ken Henderson. He has wriiten a program which is on the cd which does this and there is a whole chapter on arrays.