How to parametrize OPENQUERY in MSSQL

How to parametrize OPENQUERY in MSSQL:

MSSQL has good feature of linked server between two different Database in same location or in remote location.
OPENQUERY helps you to execute your query in Linked server (remote) .

In linked server you can directly execute your query without OPENQUERY but the performance will be a issue.

As per my experience OPENQUERY is best way to access the linked servers.

If you want to pass your variable in OPENQUERY than it is so simple. Use the following way to pass your variable in OPENQUERY.


DECLARE @strDate VARCHAR(10)
DECLARE @strSql VARCHAR(8000)
DECLARE @strLinked VARCHAR(8000)

SET @strDate= '2009-01-01'

SET @strSsql= 'SELECT * FROM TABLE1 WHERE TRN_DATE =' + '''' + '''' + @strDate+ '''' + ''''

SET @strLinked= 'SELECT * FROM OPENQUERY(MYLINKEDSERVER,' + '''' + @strSql+ '''' + ')'

--############ Print and see your script
PRINT @strLinked

--############ Final Execution
EXEC(@strLinked)

As per MSDN:

Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Advertisements