Alternatives to Linked Servers (Allow Inline SQL with Parameters)

Question

I am trying to find a solution to execute inline SQL with parameters against some linked server databases. From what I have found online the only proper way to query a linked server is to use OPENQUERY. And the only proper way to prevent SQL injection is with parameters.

However, the way OPENQUERY is structured it does not look like parameters are possible. Am I right to assume the only proper way to query with this type of configuration is with stored procedures? Are there any alternatives? Are there any similar database configuration that could be used?

Any thoughts or advice on the subject would be greatly appreciated.

Answer 1

No, you shouldn't use OPENQUERY IMHO. How about this construct, which allows you to use sp_executesql and parameters:

DECLARE @someParam INT = 5;
EXEC LinkedServerName.master.sys.sp_executesql
 @stmt = N'SELECT @i, @@SERVERNAME, @@VERSION;', 
 @params = N'@i INT', @i = @someParam;

• ThreadedLemon: This doesn't work: DECLARE @someParam VARCHAR = 5; EXEC LinkedServerName.master.sys.sp_executesql @stmt = N'SELECT * from tblTest WHERE id = @i', @params = N'@i VARCHAR', @i = @someParam; This does: select * from openquery([LinkedServerName], 'SELECT * from tblTest WHERE id = 5') The first says: Invalid object name 'tblTest'.
• Aaron Bertrand: Well, tblTest probably doesn't exist in master, right? So change master in the exec to whatever your database name is. The OPENQUERY approach works because it is setting the context to your default database. The EXEC overrides that.
• ThreadedLemon: Yeah I just saw you edit, that works. Thank you so much!