array as parameter for IN statement
Hi,
I thought TSQL of yukon would have a way of passing an array of values to a SP which can be used by it in an IN statment.
ie:
List<int> idList = new List<int>();
idList.Add(5);
idList.Add(7);
SqlCommand cmd = new SqlCommand("sp_Core_GetDetails");
cmd.Parameters.Add("@idList", idList);
cmd.ExecuteReader();
...
SP would look like (fragment)
select id, name
from DetailsTable
where id in (@idList)
is there such a feature in yukon?
Array parameter for TSQL has been dropped. However, you can use TVF to format the string (array) into rows. Thus, your query would look like this:
select id, name
from DetailsTable
where id in (select * from dbo.split(@idList))
Of course you can do something similar today with sql2k. Here is a good article on the subject by Erland.
http://www.sommarskog.se/arrays-in-sql.html
oj at 2007-9-9 >
Here is the demo code in C# from MS:
http://msdn.microsoft.com/library/en-us/dnsql90/html/sqlclrguidance.asp?frame=true#sqlclrguidance_topic4busing System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
/*
* This needs to return an IEnumerable, but since an array
* does, in this case we do not need to define a new class
* that implements it: we can simply return the array.
*/
[SqlFunction(FillRowMethodName="FillRow",
TableDefinition="value nvarchar(60)")]
public static IEnumerable GetStrings(SqlString str)
{
return str.Value.Split(';');
}
/*
* This method does the decoding of the row object. Since the
* row is already a string, this method is trivial. Note that
* this method is pointed to by the annotation on the
* GetString method.
*/
public static void FillRow(object row, out string str)
{
str = (string)row;
}
}
oj at 2007-9-9 >
