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 |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2008-02-11 : 06:40:32
|
| Hi,My stored procedure has the following parameters@CountryID int = 0,@SectorID int = 0,@SecurityID int = 0,--@EntityIDs varchar(1000) = nullAn example for @EntityIDs is null or a number such as 1 or 4 or '1,4,5,7'In the SP, there is a select query which I have been running successfully which has the following where clause:The where clause returns nothing if the commented line is used i.e. the line with @EntityIDsIf I use the @EntityIDs parameter, the query only returns data if @EntityIDs equals a number. The query does not return data if @EntityIDs is equal to character or null (i.e. not include @EntityIDs)Do you see anything wrong with the last AND ?Thanks(@CountryID = 0 or sec.Country_ID in (select items from fnSplit(@CountryID, ','))) AND (@SectorID = 0 or Sec.Sector_ID in (select items from fnSplit(@SectorID, ','))) AND (@SecurityID = 0 or Sec.Security_ID in (select items from fnSplit(@SecurityID, ','))) --AND (@EntityIDs = null or Sec.Entity_ID in (select items from fnSplit(@EntityIDs, ','))) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-11 : 06:44:29
|
quote:
Originally posted by arkiboys
Hi,My stored procedure has the following parameters@CountryID int = 0,@SectorID int = 0,@SecurityID int = 0,--@EntityIDs varchar(1000) = nullAn example for @EntityIDs is null or a number such as 1 or 4 or '1,4,5,7'In the SP, there is a select query which I have been running successfully which has the following where clause:The where clause returns nothing if the commented line is used i.e. the line with @EntityIDsIf I use the @EntityIDs parameter, the query only returns data if @EntityIDs equals a number. The query does not return data if @EntityIDs is equal to character or null (i.e. not include @EntityIDs)Do you see anything wrong with the last AND ?Thanks(@CountryID = 0 or sec.Country_ID in (select items from fnSplit(@CountryID, ','))) AND (@SectorID = 0 or Sec.Sector_ID in (select items from fnSplit(@SectorID, ','))) AND (@SecurityID = 0 or Sec.Security_ID in (select items from fnSplit(@SecurityID, ','))) --AND (@EntityIDs is null or Sec.Entity_ID in (select items from fnSplit(@EntityIDs, ',')))
change like this and try. You cant use = operator to check nulls as null is not stored as a value.Its an internal bit set used to indicate unknown value. |
 |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2008-02-11 : 06:46:17
|
| Works. Thanks |
|
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-11 : 09:57:25
|
This may perform better:Origional:(@CountryID = 0 or sec.Country_ID in (select items from fnSplit(@CountryID, ',')))AND (@SectorID = 0 or Sec.Sector_ID in (select items from fnSplit(@SectorID, ',')))AND (@SecurityID = 0 or Sec.Security_ID in (select items from fnSplit(@SecurityID, ',')))--AND (@EntityIDs = null or Sec.Entity_ID in (select items from fnSplit(@EntityIDs, ',')))Revised:(@CountryID = 0 or ',' + @SectorID + ',' Like '%,' + Cast(sec.Country_ID as varchar) + ',%')AND (@SectorID = 0 or ',' + @SectorID + ',' Like '%,' + Cast(Sec.Sector_ID as varchar) + ',%')AND (@SecurityID = 0 or ',' + @SectorID + ',' Like '%,' + Cast(Sec.Security_ID as varchar) + ',%')--AND (@EntityIDs Is Not Null or ',' + @SectorID + ',' Like '%,' + Cast(Sec.Entity_ID as varchar) + ',%') This may look odd on the surface, but let's see what it does. First, it transforms @SectorID from '1,4,5,7' into ',1,4,5,7,'. Then it takes your ID fields and transforms them to the format ',#,'. Then it does a Like. This should perform better than using a subselect with a function. |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2008-02-11 : 10:49:48
|
| I get an error if I use this new query with like.For example passing '4' or null or '4,6,7' gives the following error:"Conversion failed when converting the varchar value ',' to data type int."Thanks |
|
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-11 : 12:41:03
|
I apologize, there are a few errors. Try this:(@CountryID = 0 or ',' + Cast(@CountryID as varchar) + ',' Like '%,' + Cast(sec.Country_ID as varchar) + ',%')AND (@SectorID = 0 or ',' + Cast(@SectorID as varchar) + ',' Like '%,' + Cast(Sec.Sector_ID as varchar) + ',%')AND (@SecurityID = 0 or ',' + Cast(@ScurityID as varchar) + ',' Like '%,' + Cast(Sec.Security_ID as varchar) + ',%')AND (@EntityIDs Is Not Null or ',' + @EntityIDs + ',' Like '%,' + Cast(Sec.Entity_ID as varchar) + ',%') |
|
|
|
|
|
|
|
|
incorrect where