I have a table called MyProducts and I want to return item1 and item2
SELECT item1, item2 from MyProducts
However I want it to be filtered on a string array I pass in (from C#). This is a very big table, so I an 'IN' statement is out. How would I do this using a join statement. Thanks!
There's no reason that IN statement is "out"; ultimately, that is a perfectly reasonable way of filtering - let the optimizer worry about the various options. It certainly isn't impacted by the fact that MyProducts is large. Adding a join makes more work: it does not, however, reduce the number of "hits", or the work involved. For example, to do that with dapper is just:
string[] filter = ...
var rows = connection.Query(
"select item1, item2 from MyProducts where SomeField in @filter",
new {filter});
or with LINQ:
string[] filter = ...
var rows = db.Products.Where(x => filter.Contains(x.SomeField));
One solution is to create a temporary table and join with it. The temporary table can have an index on the column on which you will be joining.
I always liked this method...
CREATE FUNCTION dbo.Split(@String varchar(max), @Delimiter char(1))
returns @temptable TABLE (Value varchar(max))
as
begin
declare @idx int
declare @slice varchar(max)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
then you can do this...
CREATE PROCEDURE MySp
@list varchar(max)
AS
SELECT <columns>
FROM <mytable> mt
INNER JOIN dbo.split(@list,',') s ON s.Value= my.Key
NOTE: There are many Split functions out there so you do not have to use this specific one.
Another method I have used when using SQL Server 2008 is using a table parameter like this...
CREATE TYPE [dbo].[LookupTable] As Table
(
ID Int primary key
)
CREATE PROCEDURE [dbo].[SampleProcedure]
(
@idTable As [dbo].[LookupTable] Readonly
)
AS
BEGIN
SELECT <columns>
FROM <mytable> mt
INNER JOIN @idTable s ON s.Id= my.Key
END
Pass the parameter into SQL Server from C# in this manner...
DataTable dataTable = new DataTable("SampleDataType");
dataTable.Columns.Add("Id", typeof(Int32));
foreach (var id in <mycollectionofids>)
dataTable.Rows.Add(id);
SqlParameter parameter = new SqlParameter();
parameter.ParameterName="@Id";
parameter.SqlDbType = System.Data.SqlDbType.Structured;
parameter.Value = dataTable;
command.Parameters.Add(parameter);
