I need make this query:
declare @no as varchar(100)
set @no='11,2,23'
select * from ft (nolock) where fno in (@no)
but have this error all the time: Error converting data type varchar to numeric
The fno is numeric in the table
-
1@no is declared as varchar, you could do like this Exec ('select * from dbo.CM_Address (nolock) where ID in ('+@no+')')bummi– bummi2013-05-14 10:38:44 +00:00Commented May 14, 2013 at 10:38
-
1You should read this article on arrays in SQL Server sommarskog.se/arrays-in-sql-2008.html Whilst it may not provide you with exact T-SQL to solve your problem, it will certainly help you understand why your current SQL doesn't work and how you should fix it.codingbadger– codingbadger2013-05-14 10:42:44 +00:00Commented May 14, 2013 at 10:42
-
There is a difference between multiple parameters separated by commas, and a single string parameter that happens to contain commas. This is as true in SQL as it is in any other programming language I can think of. Why would you expect SQL Server to inspect the single parameter and decide to automagically convert it into multiple parameters?Damien_The_Unbeliever– Damien_The_Unbeliever2013-05-15 06:53:02 +00:00Commented May 15, 2013 at 6:53
Add a comment
|
2 Answers
You can't directly select data like in (@no) since LIKE expects data as a table. Please try:
declare @no nvarchar(max)
set @no='11,2,23'
select * from ft (nolock) where fno in (
SELECT
Split.a.value('.', 'VARCHAR(100)') AS CVS
FROM
(
SELECT
CAST ('<M>' + REPLACE(@no, ',', '</M><M>') + '</M>' AS XML) AS CVS
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a))
Comments
You can easily accomplish the query you are trying to create with a table variable instead of just a varchar variable:
declare @t table (
n int
)
insert into @t
values
(1)
,(2)
,(3)
select
*
from someTable
where n in (
select
n
from @t
)
