conditional 'where COLUMN in'

[rico14d]

Hi,

In my stored procedure i pass in a CSV list of IDs and use the IN statement to select from them.

However, sometimes i will pass in a blank string and therefore dont want to use the IN clause.

The below logic works correctly when the CSV list is blank -

where ((CategoryID in (@CategoryList) and @CategoryList <> '') or (@CategoryList = ''))

When i pass a CSV list then i get the following error -

Syntax error converting the varchar value '1108,1109,1110,1111,1112,1113,1114,1115,1116,1117' to a column of data type int.

I understand why im getting the error, does anyone know a way around this?

Rich.

 

[jbenson001]

where ((''' + CategoryID + ''' in (@CategoryList) and @CategoryList <> '') or (@CategoryList = ''))

 

[rico14d]

That didnt seem to work for the in clause, i used this instead

where '%,' + CategoryID + ',%' like @CategoryList

 

[SajidAttar]

it looks like your CategoryID field is integer. and for integer datatype your in clause should like this

  where categoryId in ('1001','1002','1003'...)

not like this

  where categoryId in ('1001,1002,1003...')

execute below code before your query

  if len(rtrim(ltrim(@CategoryID )) > 0 
     set @CategoryID = '''' + select replace(@CategoryID, ',' , ''',''') + ''''

Hope this helps.




Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[gmmastros]

There are 2 ways (that I know of) to accomplish what you are asking.

Method 1: You could use dynamic SQL

Ex.

Declare @SQL VarChar(8000)

Set @SQL = 'Select * from Table Where Ids In (' + @Ids + ')'
Execute(@SQL)

Method 2: Create a temp table to store the list of ids and then join with your table to retrieve the data you really want. There is a User Defined Function that makes this a little easier.

ALTER   Function dbo.fnSplitter (@IDs Varchar(100) ) 
Returns @Tbl_IDs Table (ID Int) As 

Begin 
 -- Append comma
 Set @IDs = @IDs + ',' 
 -- Indexes to keep the position of searching
 Declare @Pos1 Int
 Declare @pos2 Int
 
 -- Start from first character 
 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@IDs)
 Begin
 Set @Pos1 = CharIndex(',',@IDs,@Pos1)
 Insert @Tbl_IDs Select Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
 -- Go to next non comma character
 Set @Pos2=@Pos1+1
 -- Search from the next charcater
 Set @Pos1 = @Pos1+1
 End 
 Return
End

Then, to use it...

CREATE Procedure GetSomeRecords(@List VarChar(8000))
AS 
SET NOCOUNT ON

Declare @Temp Table (Id Int)
Insert Into @Temp(Id) Select ID From dbo.fnSplitter(@List)

Select 	* 
From 	<SomeTable>
	Inner Join @Temp T On <SomeTable>.Id = T.Id

Hope this helps.

-George

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