I am refactoring stored procedures that use dynamic sql.
The reason the store procedures use dynamic sql is because
the data that is need comes from another MS SQL database
that resides on the same server instance. The name of this
database is determined at runtime.

The following is a code example from a stored procedure:

declare @theDatabase sysname;
set @theDatabase = 'A database that I get based on
critera at runtime.';

declare @thePrimaryKey int;
set @thePrimaryKey = 2;

declare @theSqlString varchar(8000);

set @theSqlString = 'select r.Feild from ' +
@theDatabase +
'..TableName r ' +
'where r.ID = '
+ rtrim( str( @thePrimaryKey ) )

exec sp_executesql @theSqlString;

The problem is that dynamic sql is EXTREMELY slow! I have
been doing some research and have found a little bit on
sp_linkedservers and OPENQUERY but I have not figured out
how to accomplish what is done above.

Can anyone give me an example of how I can use
sp_linkedservers and OPENQUERY to accomplish this query?

Would it be better, performance wise, to just continue
using dynamic sql?

Any help would be greatly appreciated.

Sincerely,

John Dickey
jpd0861 (AT) msn (DOT) com

To do something similar to the statement exec sp_executesql
@theSqlString using Openquery, you would need to build the
entire Openquery statement using a string variable and pass
that into an EXEC to execute the string. You can find more
information and an example in the following article:
HOW TO: Pass a Variable to a Linked Server Query
http://support.microsoft.com/?id=314520

-Sue

On Thu, 17 Feb 2005 13:14:20 -0800, "John Dickey"
<jpd0861 (AT) msn (DOT) com> wrote: