Dynamic SQL?

[DerPflug]

I need to run a query where the results can be filtered by a value ranging from 1 to 11 or any combination thereof. For example:

SELECT * FROM MyTable WHERE Status in (1,2,3)

SELECT * FROM MyTable WHERE Status in (2,4,8,9)

How do I implement this kind of filter in the context of a stored procedure? The filter values would be entered through a VB.NET app and passed to a stored procedure. I thought about dynamic SQL but I want to be as memory-efficient as possible.

thanks in advance

 

[gmmastros]

No, don't use dynamic SQL for this.

First, create this UDF

Alter Function Split(@CommaDelimitedFieldNames Varchar(8000),@SplitChar Char(1))  
Returns @Tbl_FieldNames Table  (FieldName VarChar(100))  As  

Begin 
 Set @CommaDelimitedFieldNames =  @CommaDelimitedFieldNames + @SplitChar

 Declare @Pos1 Int
 Declare @pos2 Int
 
 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@CommaDelimitedFieldNames)
 Begin
  Set @Pos1 = CharIndex(@SplitChar,@CommaDelimitedFieldNames,@Pos1)
  Insert @Tbl_FieldNames Select  Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(100))
  Set @Pos2=@Pos1+1
  Set @Pos1 = @Pos1+1
 End 
 Return
End

Then your SP.

Create Procedure Test
  @Values VarChar(8000)
AS
SET NOCOUNT ON

Declare @Temp Table(Value Integer)

Insert Into @Temp
Select Convert(Integer, FieldName) From dbo.Split(@Values, ',')

Select * 
from   MyTable
       Inner Join @Temp T On MyTable.Status = T.Value

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Another way: pass integer bitmask to stored procedure. For example

(1, 2, 3) = 111 = 7
(2, 4, 8, 9) = 110001010 = 394

This conversion is trivial to do client-side; I guess you have buncha checkboxes in a form so looping of some kind is anyway necessary.

And then:

declare @flags int; set @flags = 394

declare @t table (Status tinyint)
declare @i int; set @i = 1

while @flags > 0
begin
	if @flags % 2 = 1 insert into @t values (@i)
	set @i = @i + 1
	set @flags = @flags /2
end

select A.*
from myTable A
inner join @t B on A.Status = B.Status

I guess approach with Split() function is more all-around... so go for it.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)