0
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%

1
  • You're using string concatenation to construct a SQL query from variables. My immediate reaction to seeing that is alarm, because it very easily leads to SQL injection vulnerabilities. Switch to parameterized queries, which usually handle this king of quoting issue out of the box for you anyway. Also, for partitioning, is there a reason this isn't hidden behind a SQL view? – jpmc26 Nov 28 '16 at 18:48
5

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.

0
7

You're missing the end quote, this should get you there.

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 generally like to use CHAR(39) in place of multiple quotes, just to make code more readable

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

Bonus thought, just as another, more readable version, you could also QUOTENAME

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.