I have a statement in the sp which does something like this.

@sql = 'SELECT OrderDate,CustID,Total,Balance from '
+ @Remoteserver
+ '.dbo.Sales.Orders where orderid =' + @orderid

exec(@sql)


The issues, which I can think with dynamic SQL, are

1. Excessive compilation of statements.
2. Due to unused compiled plan Data cache can be offloaded out of the
memory.
3. Context changes to connection login. (I am not sure if this is
expensive..is it?)
4. Ownership chaining doesn't work and need an explicit permission on
objects.

I can think of two approach to make this statement more efficient.
A] Approach 1st
1. Get all the possible values passed to @Remoteserver from the app team.
2. Create a static sp's in all the servers which takes @orderid as
parameter to select the orders table.
3. In the local server create multiple if statements to call a particular
SP.

I am sure I can eliminate the 1st and 2nd issue of dynamic query with
this. I am not sure if the 3rd and 4th issue mentioned is relevent here
when the server has to create a context to login to the remote server.


B] Approach second.
1. Use sp_executesql to parameterize the @orderid parameter. and let the
@servername parameter be part of @sql string.

I am wondering which approach is good.


-
Thanks
Shiju Samuel

Apologies upfront. I just realized I have posted it in the wrong group.

Please ignore.

-Shiju Samuel