-3

I have an application that passes data to an sql variable called @inputvar. The application passes the data as a string. The stored procedure declares the variable @inputvar as varchar. The problem is, the data is of the format '12,13,15'. because of a where clause in the stored procedure, i am getting an error saying that conversion failed when converting the varchar value to datatype int

The where cause is:

select * 
from table 
where ID in (@inputvar)

I have tried stripping the quotes: replace(@inputvar,'''','') and I have tried converting the variable to an int: cast(@inputvar as int), but both attempts failed

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
DevProg
  • 39
  • 6

1 Answers1

0

There are multiple ways to handle this; you can use dynamic query to handle your scenario

DECLARE @inputvar varchar(10)
DECLARE @sql NVARCHAR(1000)
set @inputvar = '12,13,15'

set @sql = 'select * from table where ID in (' + REPLACE(@inputvar, '''','') + ')'
EXEC @sql
techspider
  • 3,370
  • 13
  • 37
  • 61