SQL Conversion failed when converting the varchar value '*' to data type int

Question

Aside: Please note that this is not a duplicate of the countless other string-to-int issues in sql. This is a specific cryptic error message (with the asterisk) that hides another problem

Today I came across an issue in my SQL that took me all day to solve. I was passing a data table parameter into my stored procedure and then inserting part of it into another table. The code used was similar to the following:

INSERT INTO tblUsers
    (UserId,ProjectId) 
VALUES ((SELECT CAST(CL.UserId AS int) FROM @UserList AS UL),@ProjectId)

I didn't seem to get the error message when using test data, only when making the call from the dev system.

What is the issue and how should the code look?

Radu Gheorghiu · Accepted Answer · 2017-03-22 00:25:47Z

2

I could bet that (SELECT CAST(CL.UserId AS int) FROM @UserList AS UL) returns more than 1 row and your test scenario had only 1 row. But that may be just me.

Anyway, the way the code should look is:

INSERT INTO tblUsers (UserId,ProjectId) 
SELECT CAST(CL.UserId AS int),
    @ProjectId
FROM @UserList AS UL

edited Mar 22, 2017 at 0:25

user440595

answered Mar 21, 2017 at 16:27

Radu Gheorghiu

20.6k16 gold badges78 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rugnir · Accepted Answer · 2017-03-21 16:29:54Z

1

After some time of trawling through google and such places, I have determined that this SQL code is wrong. I believe the correct code is something more like this:

INSERT INTO tblUsers 
    (UserId,ProjectId) 
SELECT CAST(CL.UserId AS int) ,@ProjectId
    FROM @UserList AS UL

The issue is that the other way of doing it attempts to insert one record with the data of all of the rows in the table parameter. I needed to remove the VALUES statement. Also, in order to add other data, I can simply put that as part of the select as you would otherwise

answered Mar 21, 2017 at 16:29

Rugnir

2405 silver badges12 bronze badges

Collectives™ on Stack Overflow

SQL Conversion failed when converting the varchar value '*' to data type int

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related