Error converting data type nvarchar to bigint caused by IN operator in Stored Procedure

sql-server stored-procedures nvarchar in-operator
Miu picture Miu · Feb 18, 2015 · Viewed 8.3k times · Source

Stored Proc:

ALTER PROCEDURE [dbo].[MyProcedure]
    @CommaSeperatedValues nvarchar(500)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT  Col1, Col2, Col3
    FROM MyTable
    WHERE SomeCol_BigIntDataType IN (@CommaSeperatedValues)

The Value which is coming from Code is string value: "9010073,9010074"

I tried running the SP like this: exec MyProcedure '9010073,9010074'

This is giving the error 'Error converting data type nvarchar to bigint' while running the SP

If I run the select query separately, like this:

SELECT  Col1, Col2, Col3
    FROM MyTable
    WHERE SomeCol_BigIntDataType IN (9010073,9010074)

Then I'm getting the expected results.

But I want to run from SP.

Answer

Shobhit Walia picture Shobhit Walia · Mar 15, 2016

Here we go. You can do this by Dynamic Query

DECLARE @mystring NVARCHAR(max)
DECLARE @UserId  NVARCHAR(max)
SET @UserId = '9010073,9010074'
SELECT @mystring = 'SELECT  Col1, Col2, Col3 FROM MyTable where UserId IN('+ @UserId +')'
EXEC sp_executesql @mystring

Related questions

How does SQL Server store more than 4000 characters in NVARCHAR(max)?

I have defined a stored procedure that has a parameter of type NVARCHAR(max) and I process that string in my stored procedure. I know that max of nvarchar is 4000. But I have passed a string with 5700 characters to my …

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More