Workaround to Pass Parameter to OpenQuery using Linked Server on SQL Server

In this SQL Server database development tutorial for SQL programmers, I want to show a method which enables parameter passing to Linked Server queries using OpenQuery SQL command. OpenQuery clause does not let developers pass parameters and the OpenQuery SQL statement cannot be created dynamically. If you are looking for a solution which provides parameter usage with OpenQuery syntax, using a SQL Server stored procedure is the solution. Of course in this case, if you planned to create a SQL View based on your OpenQuery which is fetching from a remote data source via LinkedServer, solution gets complicated here.

In this SQL tutorial, you will find work-around for passing a parameter to OpenQuery SELECT query used in a SQL View.

Build Dynamic SQL Query

First step is building the OpenQuery SELECT statement dynamically as seen in following SQL script.
Please note that using single quotes within dynamic statement creation can be complicated. It is crucial to deal with single quotes successfully.


 declare @sql nvarchar(max)

 set @sql = 'select ''' + CURRENT_USER + ''' AS UserName from dummy;'

 set @sql = 'select * from OpenQuery([HANAB1P], ''' + replace(@sql,'''','''''') + ''');'

Code

Below dynamic SQL script, basically enables SQL Server developers to pass the Current_User system variable as a parameter to the SELECT query using OpenQuery syntax.
As seen, this OpenQuery command is targeted to be executed on a SAP HANA database.

dynamic SQL statement with OpenQuery and Linked Server

Execute Dynamic Query using With Result Sets

It is important to define the columns that you will get when the dynamic SQL statement is executed for following steps. You will remember we will create a SQL VIEW using a SELECT statement on a STORED PROCEDURE. So syntatically, database programmer has to identify the column structure of the dynamic SQL statement output.

SQL developers can use WITH RESULTS SETS clause with SQL EXEC command as follows:


 exec(@sql) with result sets ((username varchar(max)))

Code

You see, above we are using WITH RESULT SETS and defining the returning table structure (in this case we have a single column table output)

execute dynamic SQL with result sets clause

Create Stored Procedure Running SQL Exec Command

Let's now use the dynamic SQL generation script and execution command to create our sample Stored Procedure on SQL Server database.


 CREATE PROCEDURE HANAQueryWithSQLParameter

 AS

 

 declare @sql nvarchar(max)

 set @sql = 'select ''' + CURRENT_USER + ''' AS UserName from dummy;'

 set @sql = 'select * from openquery([HANAB1P], ''' + replace(@sql,'''','''''') + ''');'

 exec(@sql) with result sets ((username varchar(max)))

 

 GO

Code

Now, database programmers can call or execute the stored procedure and get its result set as the output of the procedure. Unfortunately, executing a stored procedure is different in a way of selecting from a SQL view. If your requirements are to use a database view (for example for some BI reporting tools, etc.), it is possible to SELECT from a STORED PROCEDURE bby using a loopback LinkedServer on SQL Server.

Create LinkedServer

In this step, with database administrators we will create a loopback LinkedServer which means a LinkedServer definition which connects to the SQL Server itself as the target data source.

A use of a loopback LinkedServer within a OPENQUERY Select statement enables execution of the stored procedure passed in the command text and displaying the output as the result of the SELECT query.

On Object Explorer window of the SQL Server Management Studio when you are connected to your SQL Server database, expand the nodes "Server Objects > Linked Servers".
Right click on the Linked Servers node and from context menu choose option "New Linked Server..."

create Linked Server on SQL Server

When the "New Linked Server" dialog screen is displayed, first make your "Server type" selection as "SQL Server"
Then in the "Linked server" textbox type "LOCALHOST" or only dot as "."

create loopback Linked Server on SQL Server

Switch to Security tab on the same screen, you will see below option "Be made using the login's current security context". Select this option and click OK to save your changes and create the loop-back linked server.

security options for SQL Server Linked Server for loopback connection

Select From Stored Procedure

As I mention in previous step, SQL developers can use the loopback LinkedServer in OpenQuery statements.
If the developer passes the stored procedure execution command as input query string value of the OpenQuery statement, the result of the procedure execution can be used in the SELECT query's FROM clause easily.
Let's have a look at following SQL code.


 SELECT query.* FROM OPENQUERY([.], 'PCS.dbo.HANAQueryWithSQLParameter') query

 -- or

 SELECT query.* FROM OPENQUERY([LOCALHOST], 'PCS.dbo.HANAQueryWithSQLParameter') query

Code

Using this method, although the OpenQuery command is static and does not accept or use any variable in a visible way, behind the sceens the stored procedure code is dynamically created with system parameters and executed.

In addition to this, use of OpenQuery with loopback LinkedServer enabled database developers to execute a SELECT query on the result set of a SQL Server stored procedure.

OpenQuery statement with loopback Linked Server on SQL Server

Create View

After SQL programmers executed and tested the SELECT query in previous step, it is very simple to wrap up the code by using a SQL View as follows:


 CREATE VIEW SQLViewOpenQuerySample

 AS

 SELECT query.* FROM OPENQUERY([.], 'PCS.dbo.HANAQueryWithSQLParameter') query

Code

Now SQL users can easily query underlying data source and pass parameters indirectly by using the workaround of loopback Linked Server.

I hope although the logic is complex this SQL tutorial helps database developers who want to pass parameter to OpenQuery method. In addition to that, SQL developers have seen the method for selecting data from stored procedure on a SQL Server database.