Pass parameter to openquery in stored procedure

Question

I have a function fn_test with 3 parameters. Then I define a stored procedure to use this function. As it is a function on a linked server, I use openquery to call it. The problem is that I do not know how to pass parameters to the function in openquery.

The stored procedure is as follow

CREATE PROCEDURE [cmn].[sp_Agent_Karmozd]
AS
BEGIN
    DECLARE @CurrentDate TDate = '2020/01/01

    INSERT INTO dbo.cmn_Agent_Karmozd
        SELECT *
        FROM OPENQUERY ([DWH], 'select * from fn_test (null, null, @CurrentDate) ')
END

Executing the script I get this error:

Msg 8180, Level 16, State 1, Line 11
Statement(s) could not be prepared.

Msg 137, Level 15, State 2, Line 13
Must declare the scalar variable "@CurrentDate"

I think, with the correction made the code should work? You should no longer get the same error message.... — Luuk, Oct 03 '20 at 09:49
[adding-parameters-to-a-stored-procedure-in-sql-server](https://stackoverflow.com/questions/52772802/adding-parameters-to-a-stored-procedure-in-sql-server) — Luuk, Oct 03 '20 at 09:52
No. this mistake has been made when copy/paste here. the source code does not have these mistakes. and the problem persist yet — Zahra, Oct 03 '20 at 09:52
It cannot persist, because there is no `@CurrentShDate` in your stored procedure. — Luuk, Oct 03 '20 at 09:53
`OPENQUERY` does not support parameters. You will have to interpolate those literally into a dynamic string. Alternatively, you can use `EXECUTE .. AT`, which does support parameters (with the `?` syntax), but that can't be combined with a local `INSERT`. — Jeroen Mostert, Oct 03 '20 at 10:29
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Oct 03 '20 at 11:52

Pass parameter to openquery in stored procedure

0 Answers0