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?

Answer 1

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

Answer 2

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