Stored Procedures And Functions

I have the following case.

I have a linked server and I want to execute SQL statements on that linked server which contains millions of records. so I decided to use SELECT * from OPENQUERY(RmtSrv,'SELECT * FROM RmtTbl WHERE Col = ' + @ColValue) but Unforetunatly the query is not succeeded because the OpenQuery doesn't accept parameters.

I used Scalar valued functions and built the query dynamically, but the Scalar valued functions doesn't execute dynamic SQL


ET @LinkedServerName = 'RmtSrv'

SET @RemoteTable= 'RmtTbl'

SET @Query = 'SELECT * FROM OPENQUERY({0}, ''SELECT Col1 FROM {1} WHERE Col2 = ''''{2}'''''') '



SET @Query = REPLACE(@Query, '{0}', @LinkedServerName)

SET @Query = REPLACE(@Query, '{1}', @RemoteTable)

SET @Query = REPLACE(@Query, '{2}', @Col2Value)

EXEC(@Query)

the above code is not executed because the functions doesn't execute dynamic queries.

I deleted the function above and wrote a Stored Procedure with the same code. but I can't query the stored procedure

SELECT GetColValue(Table1.Col1)
FROM Table1.


where the GetColValue is the name of the stored procedure.

Do you have any solution that I can use to perform Remote SQL statements considering the performancewise and the code shall be centralized in certain stored procedures and functions

the Remote server contains millions of records and I will use it from tens of applications and databases.