T-SQL Error Message 207 When passing values Dynamically

Question

I am attempting to create a stored procedure that allows values to be passed to it, however, when I try to use Dynamic T-SQL I raise an error MSG 207

I know that the error message is supposed to indicate that the column name was misspelled in my statement thanks to the following site:

https://www.tsql.info/error/msg-207-level-16-invalid-column-name.php

This, however, does not make sense as the following hard coded statement works just fine.

INSERT INTO [dbo].[tDriversLicense] (DriversLicensePrefix,    DriversLicenseSuffix, DateAdded, DriversLicenseNumber)
    VALUES ('shockc', '11653798', GETDATE(), 'GAD4859');

ALTER PROCEDURE [dbo].[spAddDriversLicense]
    -- Add the parameters for the stored procedure here
    @DriversLicensePrefix NCHAR(8),
    @DriversLicenseSuffix NCHAR(10)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

            DECLARE @sqlInsertDriversLicense NVARCHAR(MAX)
            SET @sqlInsertDriversLicense = 'INSERT INTO [dbo].[tDriversLicense] (DriversLicensePrefix, DriversLicenseSuffix, DateAdded, DriversLicenseNumber)
                VALUES (' + @DriversLicensePrefix + ', ' + @DriversLicenseSuffix + ', GETDATE(), GPC4859);'

            EXECUTE sp_executesql @sqlInsertDriversLicense

END

I've been spilling over this code for hours and I'm thoroughly confused as to why there is any difference between the two INSERT statements. The values being passed are the same but an error is raised when I attempt to pass them dynamically. How is this raising an error??

I've used some code from this video to attempt to learn how pass Scalar Values dynamically and have had luck in other stored procedures.

https://www.youtube.com/watch?v=RHHKG65WEoU

The none of the values in your dynamic SQL insert have single quotes around then. @shockemc — Mark Kram
– Mark Kram, Commented Nov 9, 2018 at 0:09
Adding single quotes to the variables would pass them as a string, defeating the purpose of passing them as parameters... Wouldn't it? @Mark Kram — shockemc
– shockemc, Commented Nov 9, 2018 at 0:42
Perhaps but that is causing your error. Print out the contents of your @/sqlInsertDriversLicense variable and you will see the problem. Either way you should consider using parameters instead of inline variables @shockemc — Mark Kram
– Mark Kram, Commented Nov 9, 2018 at 1:00
msg 207 Is your message more like this: "Msg 207 Invalid column name." ? When referring to error messages, please include the complete message with the question. (I don't memorize error messages by numeric references for every dbms out there & I'm sure I'm not alone in this.) — Paul Maxwell
– Paul Maxwell, Commented Nov 9, 2018 at 5:01
Why do you need dynamic SQL and EXEC at all? This can be solved straigth forward... — Gottfried Lesigang
– Gottfried Lesigang, Commented Nov 9, 2018 at 7:27

Paul Maxwell · Accepted Answer · 2018-11-10 01:24:56Z

2

When working with dynamic strings, select them first, so you can check that the resulting syntax is workable.

You need single quotes in the result, so you need more than single, single quotes, in the code:

declare @DriversLicensePrefix nchar(8) = 'A2345678'
declare @DriversLicenseSuffix nchar(10) = 'abcdefghij'

DECLARE @sqlInsertDriversLicense nvarchar(max)
SET @sqlInsertDriversLicense = 'INSERT INTO [dbo].[tDriversLicense] (DriversLicensePrefix, DriversLicenseSuffix, DateAdded, DriversLicenseNumber)
                VALUES (N''' + @DriversLicensePrefix + ''', N''' + @DriversLicenseSuffix + ''', ''' + convert(char(8),GETDATE(),112) + ''', ''GPC4859'');'

select @sqlInsertDriversLicense

+-------------------------------------------------------------------------------------------------------------------+
|                                                      result:                                                      |
+-------------------------------------------------------------------------------------------------------------------+
| INSERT INTO [dbo].[tDriversLicense] (DriversLicensePrefix, DriversLicenseSuffix, DateAdded, DriversLicenseNumber) |
| VALUES (N'A2345678', N'abcdefghij', '20181109', 'GPC4859');                                                         |
+-------------------------------------------------------------------------------------------------------------------+

NB You should use convert(char(8),getdate(),112) SQL Server will recognize the YYYYMMDD format as a date value regardless of server default settings.

The result see above demonstrates what the insert statement MUST be, note that it it contains several single quotes.

When you are concatenating the SQL statement, you are also dealing with strings, and every part of that has to be contained within single quotes.

So there are multiple needs for single quotes.

And; So you need multiple single quotes throughout out the concatenation, some to help form the SQL statement, and others to be INSIDE that statement.

/* get this into @sql */
select 'Y' as col1;

declare @SQL as nvarchar(max)

set @SQL = N'select ''Y'' as col1;'

select @SQL;

+---------------------+
|        @SQL         |
+---------------------+
| select 'Y' as col1; |
+---------------------+

In the larger query 2 variables are defined a NCHAR(8) or (10) as you have defined them a Nchar then when inserting data into those you should prefix that input by N

edited Nov 10, 2018 at 1:24

answered Nov 9, 2018 at 4:52

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

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

4 Comments

Gottfried Lesigang Over a year ago

I voted this up as it solves the issue, but: The better hint was to use real parameters (see my answer). Creating a command on string base is something I'd avoid if possible... (you might meet bobby tables ,-) )

shockemc Over a year ago

@Used_By_Already - I marked this as the solution as it solves the problem as it was asked. However, if you don't mind could you explain why the additional single quotes and N before the quotes where necessary?

Paul Maxwell Over a year ago

I added to the answer.

Paul Maxwell Over a year ago

I would follow Shnugo's advice use the CREATE PROCEDURE TestStraight approach and avoid dynamic sql wherever you can.

Gottfried Lesigang · Accepted Answer · 2018-11-09 07:39:16Z

2

As posted in a comment already there is no need for this dynamic approach at all.

The following code will show a straight and a dynamic approach. Try it out:

USE master;
GO
CREATE DATABASE testDB;
GO
USE testDB;
GO
CREATE TABLE TestTable(SomeContent VARCHAR(100),SomeDate DATETIME,SomeFixString VARCHAR(100));
GO

--This procedure will use the parameters directly. No need for any dynamic SQL:

CREATE PROCEDURE TestStraight(@content VARCHAR(100))
AS
BEGIN
    INSERT INTO TestTable(SomeContent,SomeDate,SomeFixString)
    VALUES(@content,GETDATE(),'Proc Straight');
END
GO

--this procedure will use real parameters. You should never use parameters in string concatenation. One day you might meet bobby tables...

CREATE PROCEDURE TestDynamic(@content VARCHAR(100))
AS
BEGIN
    DECLARE @cmd NVARCHAR(MAX)=
    N'INSERT INTO TestTable(SomeContent,SomeDate,SomeFixString)
      VALUES(@DynamicContent,GETDATE(),''Proc Dynamic'');'
    EXEC sp_executesql @cmd,N'@DynamicContent VARCHAR(100)',@DynamicContent=@content;
END
GO

--Test it

EXEC TestStraight 'yeah!';
EXEC TestDynamic 'oh yeah!';
SELECT * FROM TestTable;
GO

--Clean up

USE master;
GO
--careful with real data!
--DROP DATABASE testDB;

answered Nov 9, 2018 at 7:39

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

1 Comment

shockemc Over a year ago

@Shungo - Thank you for your input, as mentioned in an earlier comment I used dynamic SQL because I thought it was the only way to accept and use, user input in a stored procedure. I thought parameters could only be passed to functions. I am still unsure if functions can be utilized in an SP. AS for Bobby Tables, I haven't meant him in person but I purposefully left out the QUOTENAME() function I was using for the sake of simplicity. I will be attempting to solve this as you have suggested shortly.

Collectives™ on Stack Overflow

T-SQL Error Message 207 When passing values Dynamically

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related