michael.higgins (2/5/2013)
Unless i'm missing the point??The stored procedure exists on the server. The application connects to that server. If you need to change the server in the application then the stored procedure will be called based upon that connection meaning you would surely only need
SELECT * FROM dbo.order
or
SELECT * FROM mydatabase.dbo.order
Is there any need to pass in the server name and do this dynamically as each server holding the orders table would have the SP?
I guess this is because they don't want to play about with the connection strings, so instead they are using linked servers over to other servers. But as the user needs to pass in the server, the database the schema, why bother with dynamic SQL and just do it with a dynamic selection of the right connection string based on the users input/