Issue with dynamic SQL insert statement not recognizing values passed as parameter

Question

I've been trying to figure out what's wrong with this dynamic SQL insert statement. Apparently, when I pass it a string of values to insert, the error it causes makes it seem like its parsing correctly, but it says it doesn't match the number of columns specified in the statement. I've attached the table definition I'm inserting the data into, the dynamic statement, and the values parameter below:

The M2016_Field_ID column is an identity column and shouldn't need to be inserted, the same with the Enabled_ON column which has a default constraint that assigns the current date.

@valuesList = '(''description'', ''0,0'')'

SET @sql = N' INSERT INTO tblM2016_MetaData_Fields ' +
                   N' (Name, FieldNum) ' +
                   N' VALUES (@values);';
SET @params = N'@values nvarchar(max)';

IF @debug = 1
BEGIN
    PRINT @sql;
    PRINT @params;
    PRINT @valuesList;
END
ELSE
    EXEC sp_executesql @sql, @params, @values = @valuesList;

It throws the following error when I try to execute the statement:

Msg 109, Level 15, State 1, Line 3
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

You have two columns in the insert and only one column in the values. Just because a string might contain commas doesn't turn it into multiple values. — Gordon Linoff
– Gordon Linoff, Commented Jan 19, 2017 at 14:57

Pரதீப் · Accepted Answer · 2017-01-19 14:57:55Z

1

You cannot parameterize the query like that. Try this way

SET @valuesList = '''description'', ''0,0'''

SET @sql = N' INSERT INTO tblM2016_MetaData_Fields ' +
                   N' (Name, FieldNum) ' +
                   N' VALUES ('+@valuesList+');';

answered Jan 19, 2017 at 14:57

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

Comments

MikeS · Accepted Answer · 2017-01-19 14:58:32Z

1

I don't believe you can pass in a list of values as a string into a single parameter what it is probably running is something like:

insert into tblM2016_MetaData_Fields (name, fieldnum) values ('(''description'',''0.0'')') so it's trying to set name equal to the string you've set values list to. In order to do what you are trying, you'd probably have to just add that string directly into your sql statement or create two parameters (one for each field).

answered Jan 19, 2017 at 14:58

MikeS

1,7641 gold badge9 silver badges13 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2017-01-20 03:25:42Z

1

Fortunately or unfortunately, a single parameter only represents a single value in the query. T-SQL doesn't have the concept of lists.

One way around this problem is to stuff the value list inside the query string:

@valuesList = '(''description'', ''0,0'')'
SET @sql = N'
INSERT INTO tblM2016_MetaData_Fields (Name, FieldNum) 
     VALUES (@values)';

SET @sql = REPLACE(@sql, '@values', @valuesList);
IF @debug = 1
BEGIN
    PRINT @sql;
END;
ELSE
    EXEC sp_executesql @sql;

edited Jan 20, 2017 at 3:25

answered Jan 19, 2017 at 14:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Jake Over a year ago

Did you mean that @values was suppose to be @valuesList in the REPLACE?

Peter B · Accepted Answer · 2017-01-20 10:17:26Z

0

The best approach indeed is to use parameters, but you need to keep the parameters separate. Also, that way SQL Server will handle the escaping of quotes, best to never do it yourself because it's inevitable that you'll mess it up sooner or later.

The result:

SET @value1 = 'description'
SET @value2 = '0,0'

SET @sql = N' INSERT INTO tblM2016_MetaData_Fields ' +
           N' (Name, FieldNum) ' +
           N' VALUES (@NameV, @FieldNumV);';

SET @params = N'@NameV varchar(50), @FieldNumV varchar(max)';

EXEC sp_executesql @sql, @params, @NameV = @value1, @FieldNumV = @value2;

Note that you'd also need to provide a value for Enabled_ON because it is defined as NOT NULL in the table screenshot.

edited Jan 20, 2017 at 10:17

answered Jan 19, 2017 at 15:09

Peter B

24.7k5 gold badges42 silver badges94 bronze badges

2 Comments

Peter B Over a year ago

Really... a downvote? I wonder why. This is the only answer so far that keeps the parameters and thus avoids all chance of sql injection or corruption.

Jake Over a year ago

I didn't down vote you, that's for sure. Your answer just wasn't what I was looking for. By the way, I stated in the question that Enabled_ON has a default constraint so it doesn't require inclusion in the insert.

Collectives™ on Stack Overflow

Issue with dynamic SQL insert statement not recognizing values passed as parameter

4 Answers 4

Comments

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related