April 29, 2015 at 3:42 pm
All,
Having trouble w/ the following code below. First sql (select) works, but I need a list and second doesn't. I need to represent that string as 'MG', 'SF' and just returns no data. Anybody have suggestions?
use upslt
Declare @RegionID VARCHAR(100);
Set @RegionID= 'MG';
SELECT * FROM TSDBA.TS_LOCATION WHERE UPPER(LTRIM(RTRIM(REGION_ID))) in(@RegionID) ;
Set @RegionID= ' ''MG'' ,''SF'' ';
SELECT * FROM TSDBA.TS_LOCATION WHERE UPPER(LTRIM(RTRIM(REGION_ID))) in(@RegionID) ;
Thanks!!
April 29, 2015 at 4:51 pm
IN doesn't work that way. An IN takes each value in the list (in your case the variable @Region) and converts them to a series of OR statements.
Both of your selects have a single value in the IN clause so they are essentially this:
SELECT * FROM TSDBA.TS_LOCATION WHERE UPPER(LTRIM(RTRIM(REGION_ID))) = 'MG' ;
SELECT * FROM TSDBA.TS_LOCATION WHERE UPPER(LTRIM(RTRIM(REGION_ID))) = ' ''MG'' ,''SF'' '
Expressed like that, I'm sure you can see why the second is returning no data, Either change your design, or look up the Delimited8kSplit function and use that.
btw, the functions on the Region_ID column ensure that these two queries will execute with a table scan and hence will perform terribly on larger tables. Unless your DB is case-sensitive, the UPPER has no purpose. SQL ignores trailing spaces so the RTRIM has no purpose at all. If you have leading whitespace in your columns consider fixing the data rather than hobbling the query performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply