Need Help with OpenQuery

[erude99]

here's the line of code


SELECT * FROM OPENQUERY(PDWADDV_TRP, 'SELECT DATA FROM MAP_TICKLER_LIST_DETAIL WHERE SEQUENCE_NUMBER= ' + '''' + '''' + @variable + '''' + ''' AND TICKLER_DATE= "@TicklerDate" ORDER BY DATA')


error message:


Msg 102, Level 15, State 1, Procedure usp_Bunn_BBH_Positions, Line 34
Incorrect syntax near '+'.

 

[simian336]

gussing error in your dynamic sql. Create it in a variable first so you can print it out.

declare @variable as varchar(20)
declare @sqlstr as varchar(2000)
declare @TicklerDate as varchar(12)

set @variable = 'test'
set @TicklerDate=cast(getdate() as varchar)


set @sqlstr='SELECT DATA FROM MAP_TICKLER_LIST_DETAIL WHERE SEQUENCE_NUMBER = '
+ '''' +@variable + ''''
+ ' AND TICKLER_DATE = ' + '''' + @TicklerDate + '''' +' ORDER BY DATA'

SELECT * FROM OPENQUERY(PDWADDV_TRP, @sqlstr)


I also like to use char(39) instead of ''''

set @sqlstr='SELECT DATA FROM MAP_TICKLER_LIST_DETAIL WHERE SEQUENCE_NUMBER = '
+ char(39) + @variable + char(39)
+ ' AND TICKLER_DATE = ' + char(39) + @TicklerDate + char(39)
+ ' ORDER BY DATA'


Simi