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?