Anilrgowda

Question: In reference to my question that was previously answered at:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22595427.html

I am now having problems further down my stored procedure with what appears to be the same situation, but the above solution does not apply.

Again, I am adapting this SP from SQL 7.0 to SQL 2005.

Here is the part is failing at now:

-----------------------
SELECT @SQL = 'SELECT "' + @NAME + '", CRAGENTID, count(DISTINCT L.I3_ROWID)
FROM ' + @NAME + '_CALLLIST L
WHERE CRCallResultCode like "S51"
AND CONVERT(VARCHAR(12), crcalldatetime, 110) between "' + @startdate + '" and "' + @enddate + '"
GROUP BY CRAGENTID'

-------------------------------

If I wrap S51 in single quotes, I get a syntax error while altering the SP. If I use double quotes, the SP gets modified correctly, but when executed, I get "invalid column name S51'. S51 is a value in the Column CRCallResultCode column.... so I am confused. Any guidance would be appreciated!

------------------

Anilrgowda

you need to use two single quotes to enclose what will eventually be a literal...

ie ''S51'' not "S51"

the 3 quotes in the
AND CONVERT(VARCHAR(12), crcalldatetime, 110) between "' + @startdate + '" and "' + @enddate + '"

are actually (intended to be) 3 single quotes ... because you are using dynamic sql (A bad idea usually)
you need to contruct the sql string with embeded quotes around your literal strings...

when doing this you double up / escape the quote character so that the sql parser recognisez that you want to actually use a quote within the string rather than end the current string...

you probably want this

SELECT @SQL = 'SELECT ''' + @NAME + ''', CRAGENTID, count(DISTINCT L.I3_ROWID) '
+ ' FROM [' + @NAME + '_CALLLIST] as L '
+ ' WHERE CRCallResultCode like ''S51'''
+ ' AND CONVERT(CHAR(12), crcalldatetime, 110) between '''
+ @startdate + ''' and ''' + @enddate + ''''
+ ' GROUP BY CRAGENTID'

------------------