I have a table like this with the data


Case SpeciesID

112 1

223 2

121 1

211 Null

200 2

Now I am getting the string like this from front end


Now i need to get data from this table which are speciesid 1 and Null only.

My query look like this:

Declare @Species varchar(10)

set @species='1,Null'

select * from paPatient p

where ('%,'+@Species+',%' like '%,'+cast(p.paSpeciesID as nvarchar(max))+',%')

I am getting only SpeciesID =1 not Cases which are having SpeciesID as null.How to get that Null cases information.

Thanks in advance.

Re: Getting Nulls

Arnie Rowland

A couple of different issue here.

You are attempting to handle @Species as an ARRAY. SQL Server isn't capable of using an array. (There are some kludges around however.)

You are adding wildcards to each side of your field value, and by so doing will remove any possibility of using indexing.

NULL can NEVER be LIKE NULL. It is impossible to evaluate NULL. It just is.

IF you want your query to check for NULL values also, then something like this (@Species MUST contain only one (1) value):

Code Snippet





FROM paPatient

WHERE ( paSpeciesID = @Species

OR paSpecies IS NULL


Re: Getting Nulls



Declare @Species varchar(10)

set @species='1,0'

I solved it like the following:

And ('%,'+@Species+',%' like '%,'+cast(isnull(pp.paSpeciesID,0) as nvarchar(max))+',%')


Re: Getting Nulls

Arnie Rowland

That could work for values other than NULL values passed in the parameter.

Also, be aware, you will not be using indexes, and the query execution may be less than optimal.

Re: Getting Nulls



From my Sqlserver reporting Services report I have the multivalued parameter from that multi valued parameter I am getting the Species value like in the format:'0,1,-1' here -1 is for if null is selected.

So I am comparing like this

@Species='0,1,-1' this is from my report.

select * from patient pp

Where ('%,'+@Species+',%' like '%,'+cast(isnull(pp.paSpeciesID,-1) as nvarchar(max))+',%')

Any better approach is available for this comparision.

Thanks in advance