Uniqueidentifier value insert dynamic in sql execution

Question

I want to insert value dynamically in Exec call in SQL Server.

DECLARE @TableName NVARCHAR(MAX) = 'tblTestInsert'
DECLARE @SQLString NVARCHAR(MAX)
DECLARE @Id UNIQUEIDENTIFIER = '3781EF06-6EE4-41ED-81C5-9AE7AD4C4356'

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName)
BEGIN
    SET @SQLString = 'INSERT INTO ' + @tableName + '(Id, FirstName, LastName, Description) VALUES ('+ CAST(@Id AS UNIQUEIDENTIFIER) + ',"visual","studio","visual studio 2017")';
    EXEC sp_executesql @SQLString
END
ELSE
BEGIN
   PRINT 'Table is not exists';
END

But it throws the below error:

The data types nvarchar(max) and uniqueidentifier are incompatible in the add operator.

Please help with this query. Thanks in advance!

@AlwaysLearning error throw like Incorrect syntax near 'F06' after use CAST(@Id AS NVARCHAR(MAX)) — debugger
– debugger, Commented Aug 26, 2019 at 11:36
@debugger make sure to enclose the literal value with single quotes. — EzLo
– EzLo, Commented Aug 26, 2019 at 11:39
@EzLo i'm tried query (based on your query) like SET SQLString = 'INSERT INTO ' + tableName + ' (Id,FirstName,LastName,Description)VALUES(CONVERT(UNIQUEIDENTIFIER,' + CONVERT(VARCHAR(100), Id) + '),"visual","studio","visual studio 2017")'; error throw Incorrect syntax near 'F06'. — debugger
– debugger, Commented Aug 26, 2019 at 11:46

EzLo · Accepted Answer · 2019-08-26 11:40:21Z

You can't concatenate UNIQUEIDENTIFIER with NVARCAHR without a conversion.

DECLARE @Id UNIQUEIDENTIFIER = '3781EF06-6EE4-41ED-81C5-9AE7AD4C4356'

SELECT
    N'This is the ID: ' + @Id

Msg 402, Level 16, State 1, Line 4 The data types nvarchar and uniqueidentifier are incompatible in the add operator.

You want to "rewrite" your unique identifier as a nvarchar so the dynamic SQL correctly interprets it back. Remember that unique identifier isn't text like nvarchar, it's a bunch of 16 bytes.

DECLARE @Id UNIQUEIDENTIFIER = '3781EF06-6EE4-41ED-81C5-9AE7AD4C4356'

SELECT
    N'This is the ID: ' + CONVERT(NVARCHAR(100), @Id)

Actually I'd explicitly cast to UID on the dynamic part:

SET @SQLString = N'
    INSERT INTO ' + @tableName + N' (
        Id, 
        FirstName, 
        LastName, 
        Description) 
    SELECT 
        CONVERT(UNIQUEIDENTIFIER, ''' + CONVERT(NVARCHAR(100), @Id) + N'''),
        ''visual'',
        ''studio''
        ''visual studio 2017''';

Dan Guzman · Accepted Answer · 2019-08-26 11:53:06Z

1

Rather than build the SQL statement with a literal string, use a parameterized query like below. Also, it would be best to use ISO SQL standard single quotes so that the query will run regardless of the QUOTED_IDENTIFIER session setting. Using double-quotes requires SET QUOTED_IDENTIFIER OFF and is incompatible with SQL Server features line indexed views, filtered indexes, et. al.

DECLARE @TableName NVARCHAR(MAX) = 'tblTestInsert'
DECLARE @SQLString NVARCHAR(MAX)
DECLARE @Id UNIQUEIDENTIFIER = '3781EF06-6EE4-41ED-81C5-9AE7AD4C4356'

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName)
BEGIN
    SET @SQLString = 'INSERT INTO ' + QUOTENAME(@TableName) + '(Id, FirstName, LastName, Description) VALUES (@Id,''visual'',''studio'',''visual studio 2017'');';
    EXEC sp_executesql @SQLString, N'@Id UNIQUEIDENTIFIER', @Id = @Id;
END
ELSE
BEGIN
   PRINT 'Table does not exist';
END;

answered Aug 26, 2019 at 11:53

Dan Guzman

46.9k3 gold badges52 silver badges77 bronze badges

2 Comments

debugger Over a year ago

above code is working fine. Thanks for your support. I have one more doubt. if one or more unique identifier column using in this query, how can i insert the value. Can you please help this doubt?

Dan Guzman Over a year ago

@debugger, you can simply specify additional parameters with different names. For example,

SET @SQLString = 'INSERT INTO ' + QUOTENAME(@TableName) + '(Id, FirstName, LastName, Description, Guid) VALUES (@Id,''visual'',''studio'',''visual studio 2017'', @Guid);';     EXEC sp_executesql @SQLString, N'@Id UNIQUEIDENTIFIER, @Guid UNIQUEIDENTIFIER', @Id = @Id, @Guid = @Guid;

Collectives™ on Stack Overflow

Uniqueidentifier value insert dynamic in sql execution

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related