SQLTeam.com | Weblogs | Forums

Stored procedure to linked server with function parameter containing quotes


#1

Found this code on your site. Are all the quotes single or are there some doubles in there?
Thanks!

Tested below code

declare @wcID char(4)
declare @sql_str nvarchar (4000)

SET @wcID = '1'

SET @sql_str =
'SELECT COUNT(coil_num) AS coilCnt,
SUM(produced_wgt) AS weight,
SUM(produced_length) AS length,
SUM(bonus_base_pct) AS bonus
FROM MIPS.coil_dtl
WHERE wc_num = ''' + @wcID + '''
AND coil_num = coil_num_at_wc
AND to_char(produced_date_time, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-12-15 07:00:00''
AND to_char(produced_date_time, ''YYYY-MM-DD HH24:MI:SS'') < ''2005-12-15 19:00:00'''

SET @sql_str = N'select * from OPENQUERY(MIPS, ''' + REPLACE(@sql_str, '''', '''''') + ''')'

PRINT @sql_str

EXEC (@sql_str)


#2

Single.

Since this is going into a variable, you need to qualify the quote.


#3

Thanks! :slight_smile: