Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mahimam_2004
Starting Member
40 Posts |
 Posted - 2007-03-30 : 12:59:45
|
| Hi, I have a table like this with the datapatientCase SpeciesID 112 1 223 2 121 1 211 Null 200 2 Now I am getting the string like this from front end @Species='1,Null'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 pwhere ('%,'+@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. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-30 : 13:08:05
|
You can't just use a LIKE comparision for this query, even without the NULL problem, you'll get incorrect matches, for example, if the @species value was '1' then it would match patients with a species of 1 or 10 or 11 or 12 etc. (anything with a 1 in it).You need to parse that string into its parts and then query them individually. There is a nice example herehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81422To handle the NULL you'll need to check for that when you parse the string, so in that example I referred you to, changeINSERT INTO @t SELECT @array_value toINSERT INTO @t SELECT CASE WHEN UPPER(@array_value) = 'NULL' THEN NULL ELSE @array_value END |
 |
|
|
|
|
|
|
|
Getting Nulls information