Insert row with uniqueidentifier

This is a snippet from a stored proc that creates a database. We have a table with uniqueidentifier PK. I’m trying to insert a row with an empty guid as the PK:

DECLARE @MailingListId uniqueidentifier
SET @MailingListId = (SELECT cast('00000000-0000-0000-0000-000000000000' AS uniqueidentifier))
DECLARE @MailingListName  varchar(15)
SET @MailingListName = 'General'

SET @str = ('INSERT [' + @db_name + '].[dbo].[MailingLists] ([MailingListId], [MailingListName], [Selected]) 
VALUES (' + @MailingListId + ', ''' + @MailingListName + ''', 0)')
exec (@str);

The error messages are

Msg 156, Level 15, State 1, Line 1198
Incorrect syntax near the keyword 'VALUES'.
Msg 105, Level 15, State 1, Line 1198
Unclosed quotation mark after the character string ')

Can’t see what’s wrong with the VALUES clause but I guess it has something to do with the final quote, although the final quote is a closing quote not an opening quote. Maybe it’s the way I assign the empty guid to @MailingListId? Should @MailingListId be quoted?

Answer

You should be using QUOTENAME for the database name, and proper parameters via sp_executesql. You can also shorten the empty guid to CAST(0x0 AS uniqueidentifier)

DECLARE @MailingListId uniqueidentifier = cast(0x0 AS uniqueidentifier);
DECLARE @MailingListName varchar(15) = 'General';

DECLARE @str nvarchar(max) =
N'INSERT ' + QUOTENAME(@db_name) + N'.[dbo].[MailingLists] ([MailingListId], [MailingListName], [Selected]) 
VALUES (@MailingListId,@MailingListName, 0);';

PRINT @str;  -- for debugging
exec sp_executesql
    @str,
    N'@MailingListId uniqueidentifier, @MailingListName varchar(15)',
    @MailingListId = @MailingListId, @MailingListName = @MailingListName;