Dynamic SQL Insertion Script Error: Invalid Column Name

Question

I have a stored procedure written to insert a new row to a table and I wish to generalize it so a single procedure may add rows to any of a range of tables depending on the input. Here is the query which presently defines the individual case:

CREATE PROCEDURE [Test].[NO001CreateRow]
@InstanceID char(5)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.NO001 (InstanceID)
VALUES (@InstanceID)
END
GO

This seems to work fine. InstanceID is the name of a key-value column in the table NO001. The procedure inserts a new row into NO001 with the value of InstanceID set to the input @InstanceID.

Here is the query which defines the procedure I have made for the general case:

CREATE PROCEDURE [Test].[GenCreateRow]
@TableID nchar(5),
@InstanceID nchar(5)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max) = 'INSERT INTO dbo.' + @TableID + ' (InstanceID)
VALUES (' + @InstanceID + ')'
EXEC sp_executesql @sql
END
GO

As far as I can tell, this procedure should add to a table identified by the input five-character @TableID a new row with column InstanceID populated by the input @InstanceID. However, when I run the procedure with parameters @TableID=NO001 and @InstanceID=XXXXX the code breaks and returns the error message Invalid column name 'XXXXX'.

I do not understand why it tries to find a column in table @TableID titled @InstanceID. I understood the column definition to be the parenthetical InstanceID on line 7, and I don't believe InstanceID should automatically assume the value of @InstanceID. Does anyone know what's going on here?

Here is the code displayed on execution:

DECLARE @return_value int
EXEC @return_value = [Test].[GenCreateRow]
@FormID = N'NO001',
@InstanceID = N'XXXXX'
SELECT 'Return Value' = @return_value
GO

Change EXEC to PRINT and have a look. Might just be missing quotes around your value strings. — Hart CO
– Hart CO, Commented Aug 6, 2014 at 16:36

bowlturner · Accepted Answer · 2014-08-06 16:43:24Z

3

InstatnceID is a nchar, so in the values you need quotes denoting a string value in the command you are putting together.

 DECLARE @sql nvarchar(max) = 'INSERT INTO dbo.' + @TableID + ' (InstanceID) VALUES (''' + @InstanceID + ''')'

it makes your code you run from this

INSERT INTO dbo.TableName (InstanceID) VALUES (IDVALUE)

to

INSERT INTO dbo.TableName (InstanceID) VALUES ('IDVALUE')

when you execute it.

edited Aug 6, 2014 at 16:43

answered Aug 6, 2014 at 16:36

bowlturner

2,0264 gold badges26 silver badges38 bronze badges

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

Comments

Robert · Accepted Answer · 2014-08-06 16:43:59Z

2

Try to add double apostrophe

CREATE PROCEDURE [Test].[GenCreateRow]
@TableID nchar(5),
@InstanceID nchar(5)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max) = 'INSERT INTO dbo.' + @TableID + ' (InstanceID)
VALUES (''' + @InstanceID + ''')'
EXEC sp_executesql @sql
END
GO

It works because your code do this:

INSERT INTO dbo.Table  (InstanceID) VALUES (XXX)

and my code do this:

INSERT INTO dbo.Table  (InstanceID) VALUES ('XXX')

edited Aug 6, 2014 at 16:43

answered Aug 6, 2014 at 16:37

Robert

25.8k8 gold badges70 silver badges86 bronze badges

1 Comment

Robert Over a year ago

@Bibliophael I've added explanation.

Collectives™ on Stack Overflow

Dynamic SQL Insertion Script Error: Invalid Column Name

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related