SQL server query Error

Question

declare @ID as nvarchar(50)=''

SET @ID='1,2,3'

Select * from Orders_tbl where OrderId in (@ID)

Showing An error-

Msg 8114, Level 16, State 5, Line 3

Error converting data type nvarchar to numeric.

But working fine with direct values.

Suthish Nair · Answer

Ravi You cannot a string data type value to number because OrderId is a numeric data type.

Jignesh Trivedi · Answer

hi Agree with sanjeeb 's solution. Alternative you can use split fubnction which inbuilt in SQL server 2008. declare @ID as nvarchar(50)='' SET @ID='123' Select * from Orders_tbl where OrderId in (select value from fn_split(@ID'')) hope this will help you.

Sanjeeb Lenka · Answer

hi try this: declare @ID as nvarchar(50)='' DECLARE @d VARCHAR(MAX) SET @ID='123' set @d = 'Select * from Orders_tbl where OrderId in('+@ID+')' exec (@d)