sqlsql-serversql-server-2012dynamic-sql

Unable to compare a string with a UNIQUEIDENTIFIER

The following dynamic SQL snippet is comparing a string with a UNIQUEIDENTIFIER in the condition.

ALTER PROCEDURE [dbo].[GetLocationOfGuidPre] 
    @GuidArgument UNIQUEIDENTIFIER
    /* Script continues... */
    SET @SQL_String = 'INSERT INTO #Guids(FoundGuid) SELECT ' + @ColName + ' FROM ' + @TableSchema + '.' + @TableName + ' WHERE ' + @ColName + ' = ' + @GuidArgument;

Executing this script throws an error: The data types nvarchar and uniqueidentifier are incompatible in the add operator.

What is the way to compare a string with a UNIQUEIDENTIFIER?

Solution

  • Have you tried passing it as a parameter?

    SET @SQL_String = 'INSERT INTO #Guids(FoundGuid) SELECT ' + @ColName + ' FROM ' + @TableSchema + '.' + @TableName + ' WHERE ' + @ColName + ' = @GuidArgument';

EXEC sp_executesql @SQL_string,
                   N'@GuidArgument UNIQUEIDENTIFIER',
                   @GuidArgument = @GuidArgument;