February 28, 2008 at 9:11 am
I need to do the following:
DECLARE @IdColumn NVARCHAR(20)
SET @IdColumn = '1,3,5'
select * from table where table. IdColumn in (@IdColumn)
The problem is that table.Idcolumn is declared as smallint, so i got the error 'Conversion failed when converting the nvarchar value '1,3,5' to data type smallint.' I've already try to cast the column to nvarchar(20), it doesn't gives me the error but also i don't get any row, if anyone knows how to solve this, without having to use EXEC 'theQuery' please let's share the knowledege 🙂 greetings
February 28, 2008 at 9:26 am
The only option other than dynamic SQL (which you said you don't want) is to look up one of the split functions here (should be one in the script section) and use that.
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
February 28, 2008 at 9:37 am
Wow. This question has been coming up alot. I tend to lean on xml, in situations such as this. It give the ability to parse data really quickly while not using the in clause.
DECLARE @IdColumn NVARCHAR(20)
SET @IdColumn = '1,3,5'
DECLARE @x XML
SET @x = '<i>' + REPLACE( @IdColumn, ',', '</i><i>') + '</i>'
SELECT *
FROM MyTAble a
INNER JOIN
(SELECT x.i.value('.', 'INT') AS [ID]
FROM @x.nodes('//i') x(i)
) AS b
ON a.ID = b.ID
February 28, 2008 at 12:26 pm
Alright, i'm gonna use a split function, it works for me because I only have to split a few values, and also thanks To adam, i hope to start using more XML in the future, see you amigos! good day
February 28, 2008 at 2:36 pm
Alright, i'm gonna use a split function, it works for me because I only have to split a few values, and also thanks To adam, i hope to start using more XML in the future, see you amigos! good day
Keep in mind that using a function in the where clause makes SQL evaulate the function for every row. You are better off inner joining the return on the function to the main table.
February 28, 2008 at 3:38 pm
I'm not sure what you mean, i did :
select * from table where table. IdColumn in (Select Item From dbo.Split(@idColumn, ',')), what's the problem with that? can bring more rows that it should or it's a performance problem? Do you say that i should use
select * from table inner join dbo.Split(@IdColumn,',') Values ON table.IdColumn = values.Items ? is that legal?
February 28, 2008 at 3:41 pm
Assuming your split function returns a table - I do believe so. Looks legit to me.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 28, 2008 at 3:45 pm
Yep, that is exactly what I mean. This will help with performance. Especially if your table has a lot of records.
February 28, 2008 at 3:46 pm
But as Matt said, this only works if you are returning a table.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply