June 26, 2015 at 9:38 am
Hi All,
I am trying to add a where condition on an ID column(type - INT) with values coming from a variable (type - STRING). i am using cast to cast the ID as Varchar and then apply the condition, but i am not getting any results back. following is an example of what i am trying to do.
using temp table in the example , so you can copy the t-sql and run as is.
-------------------------------------------------------------------------------------------------
CREATE TABLE #TABLE1(ID INT)
INSERT INTO #TABLE1 VALUES (1), (2) , (3) , (4)
DECLARE @ID varchar(8000) = '2,4'
DECLARE @newID varchar(8000) = ''''+@ID+''''
set @newID = LEFT(RIGHT( @newID, LEN( @newID)-1),LEN( @newID)-2)
PRINT @newID
--Query 1
select * from #TABLE1
where cast(ID as varchar(8000)) in (@newID)
--Query 2
select * from #TABLE1
where cast(ID as varchar(8000)) in (2,4)
DROP TABLE #TABLE1
--------------------------------------------------------------------------------------------------------------------
Query 1 - not getting any records
Query 2 - Getting back 2 rows with 2,4 as ID values.
what else can i do to get query 1 working? any help is appreciated.
June 26, 2015 at 9:43 am
It's not data types that are the problem. It's that IN doesn't work that way.
Column IN (@Variable), because there's only one variable in the brackets, means Column = @Variable, and you have no rows where the ID = '2,4', obviously.
You need either dynamic SQL (not recommended) or a string splitter function like the DelimitedSplit8k (google for it).
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
June 26, 2015 at 9:47 am
You need to do something like this:
select * from #TABLE1
where ID in (select Item from dbo.DelimitedSplit8K(@newID,',');
The code for dbo.DelimitedSplit8K is attached.
June 26, 2015 at 1:17 pm
myjobsinus (6/26/2015)
Thanks i created a function to split the string and that worked.
Don't write your own, odds are it's using one of the more inefficient methods. Use the one I recommended and Lynn attached.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply