Set a Value in Verbal using sql

Question

I want to set "InOperator" value in a Verbal. This is my select Query.

DECLARE @i nvarchar(Max); set @i = '619, 618, 620, 2162, 2173, 3613, 2090';
select *  from tbl_PharmacypurDetail where ItmCode in (CAST(@i as bigint))

Result

Msg 8114, Level 16, State 5, Line 2
Error converting data type nvarchar to bigint.

Solution

You can use STRING_SPLIT to splits a string into rows of substrings, based on a specified separator character.

DECLARE @i nvarchar(Max); set @i = '619, 618, 620, 2162, 2173, 3613, 2090';
select *  from tbl_PharmacypurDetail where ItmCode in (SELECT value FROM STRING_SPLIT(@i, ','))

Comments

[Larnu]

The error is telling you the problem. '619, 618, 620, 2162, 2173, 3613, 2090' isn't a int, it's a varchar of numerical characters, whitespace and commas. What version of SQL Server are you using?

[Waqar YousufZai]

i am using sql server r2. and i want to use these values IN operator helping with a variables.

[Larnu]

"server r2"? Do you mean SQL Server 2008 R2 (which is completely unsupported)?