CODEWITHSUNDEEP
sql-servert-sql
BAbreu
BAbreu

Reputation: 3

SQL Error converting data type varchar to numeric

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

Upvotes: 0

Views: 737

Answers (2)

vvvv4d
vvvv4d

Reputation: 4095

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
)

Upvotes: 0

TechDo
TechDo

Reputation: 18629

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))

Upvotes: 4

Related Questions