"Conversion failed when converting the nvarchar value '113332,113347' to data type int."

Question

I am getting error for the below code. I am passing local ids as "113332,113347"

cn.Open();
SqlCommand cmd = new SqlCommand("SelectUser", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@in_UserId", SqlDbType.Int).Value = Convert.ToInt32(userId);
cmd.Parameters.Add("@in_StartDate", SqlDbType.DateTime).Value = fromDate;
cmd.Parameters.Add("@in_EndDate", SqlDbType.DateTime).Value = toDate;
cmd.Parameters.Add("@in_LocalIds", SqlDbType.NVarChar, 100).Value = localids.ToString();
cmd.ExecuteNonQuery();

In database, the local id is of datatype int.

The stored procedure code is given below

CREATE PROCEDURE [dbo].[User_Update] @in_UserId INT
,@in_StartDate DATETIME
,@in_EndDate DATETIME
,@in_LocalIds NVARCHAR(100)
AS
BEGIN
SELECT * FROM TABLE1 WHERE LocalId in (@in_LocalIds) AND UserId = @in_UserId
END
go

So what is in `userId` when it fails? Looks like it's not an int. — LordWilmore, Sep 02 '16 at 10:42
Can you post the stored procedure? Also what type is `localids` in your code ? — Bojan B, Sep 02 '16 at 10:44
Sounds to me like you have a bug and you're assigning the same string value "113332,113347" to both `localids` and `userid` — rory.ap, Sep 02 '16 at 10:44
Its not a bug, you cant parameterize the `IN()` part of a query like that. — Jamiec, Sep 02 '16 at 11:14

owczarek · Accepted Answer · 2016-09-02T11:18:25.147

In the @in_LocalIds parameter you have the int joined by ',', so split the values (in SQL it should be some kind of while loop), convert the values to int and insert them to the temporary table.

Then use the table in your where clause.

EDIT:

CREATE PROCEDURE [dbo].[User_Update] @in_UserId INT
,@in_StartDate DATETIME
,@in_EndDate DATETIME
,@in_LocalIds NVARCHAR(100)
AS
BEGIN

declare @tempLocalIds nvarchar(100)
declare @tempStrId nvarchar(100)
declare @tempId int
declare @idx int
declare @Ids TABLE ( ID int )

set @tempLocalIds = @in_LocalIds

while( len(@tempLocalIds) > 0)
begin
    -- find index of first ',' char
    set @idx = charindex(',', @tempLocalIds)
    -- get substring 0 to previously found index
    set @tempStrId = substring(@tempLocalIds, 0, @idx-1)
    -- convert the value
    set @tempId = convert(@tempStrId as int)
    -- remove the first number from string
    set @tempLocalIds = substring(@tempLocalIds, @idx, len(@tempLocalIds) - @idx -1) 


    -- insert into the temp table
    insert into @Ids(ID)
    values (@tempId)

end


SELECT * FROM TABLE1 WHERE LocalId in (select ID from @Ids) AND UserId = @in_UserId
END
go

I am getting this error when i run the stored procedure - Invalid length parameter passed to the LEFT or SUBSTRING function. The while loop is going never ending. — CPK_2011, Sep 02 '16 at 11:52
Add several prints: `print @tempLocalIds`, `print @idx` and `print len(@tempLocalIds)` before the last substring to check if the system is not trying to get the substring of the length greater than the total string length. — owczarek, Sep 02 '16 at 12:25

"Conversion failed when converting the nvarchar value '113332,113347' to data type int."

1 Answers1