SQL Server – Fixing Unclosed Quotation Mark Error

sql servert-sql

WHILE (@7DaysEarlierPartitionIntegerId <= @CurrentPartitionIntegerId)
    BEGIN
        Set @7DaysEarlierPartitionId 
            = CAST(@7DaysEarlierPartitionIntegerId AS char)
        set @sqlCommand 
            = 'Select * from ' 
              + quotename(@RequestUsage_Partition + @7DaysEarlierPartitionId)
              + 'where UserLogin like''r2\rohit.kharade'
        exec(@sqlCommand)
        set @7DaysEarlierPartitionIntegerId 
            = @7DaysEarlierPartitionIntegerId + 1
    END

I get the error:

Unclosed quotation mark after the character string 'r2\rohit.kh'.

How to execute the exec command with UserLogin =%r2\rohit.kharade%

Best Answer

As an alternative, you could parameterize the argument so that you don't have to deal with quotes at all.

DECLARE @likeArg varchar(255) = 'r2\rohit.kharade';

WHILE (@7DaysEarlierPartitionIntegerId <= @CurrentPartitionIntegerId)
BEGIN
    Set @7DaysEarlierPartitionId 
        = CAST(@7DaysEarlierPartitionIntegerId AS varchar(11)) --*
    set @sqlCommand = N'Select <output columns> from ' 
          + quotename(@RequestUsage_Partition + @7DaysEarlierPartitionId)
          + N' where UserLogin like @likeArg ORDER BY <sort columns>;'
    exec sys.sp_executesql @sqlCommand, N'@likeArg varchar(255)', @likeArg; 
    set @7DaysEarlierPartitionIntegerId += 1;
END

* Also, don't use char/varchar etc. without length.

Related Question