OPENQUERY in table-valued function with parameters

Question

I am new in using OPENQUERY and I have encountered a problem when I use it inside a table-valued function, below is my query

 INSERT INTO @returnList 
     SELECT * 
     FROM OPENQUERY(lnk_db, 'SELECT s.posting_date -COALESCE(max(a.due_date::date), l.grant_date) as loan_age
                             FROM ln.loan_snapshot_active s
                             INNER JOIN ln.loan_active l ON l.id = s.loan_id
                             LEFT JOIN ln.amortization_schedule a ON a.loan_id =  l.id
                                                                  AND a.status = ''PAID''
                                                                  AND a.due_date < s.posting_date
                             WHERE s.posting_date = ''2020-01-07''::date
                             GROUP BY l.id, s.posting_date, l.grant_date');

It works well when I just put the date directly in where s.posting_date = ''2020-01-07''::date

but when I approach it dynamically I always encountered an error.

I have to pass the date chosen by the users and I do it this way

WHERE s.posting_date = ''''' +@postingdate+'''''::date

but I always encountered an error. (In this code I encountered an error stating Incorrect syntax near '+'.)

Can anyone help me with this? I already tried the other solution but it never works.

Thanks in advance

Please edit your question and include the error you are encountering. Are you getting an error message? — TT., Jan 20 '20 at 07:39
`OPENQUERY` supports neither parameters nor expressions. You have to build the *entire* query involving `OPENQUERY` dynamically. Consider using `INSERT ... EXECUTE (...) AT ...`, which does support parameters, albeit only with the `?` placeholder syntax. — Jeroen Mostert, Jan 20 '20 at 09:46
@JeroenMostert is there any alternative sir instead of using openquery? — pryxen, Jan 21 '20 at 00:41

score 0 · Answer 1 · answered Jan 20 '20 at 09:42

0

Try

WHERE s.posting_date = ''' + @postingdate +'''

Also, what is the ::date meant to do? I'm not familiar with that annotation.

answered Jan 20 '20 at 09:42

Ethan1701

193
1
10

that `::date` is a converter in postgresql – pryxen Jan 21 '20 at 00:38

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

As per this answer, OPENQUERY does not accept variables for its arguments.

But there is a workaround posted in that answer.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 21 '20 at 12:21

Ethan1701

193
1
10

OPENQUERY in table-valued function with parameters

2 Answers2