adonetok (2/5/2013)

---

Below is statement for a table

select * from myserver.mydatabase.dbo.order

I want to declare a path to modify above statement but got an error.

declare @mypath varchar(50)
set @mypath = 'myserver.mydatabase.dbo.'

select * from @mypath + 'order'

declare @mypath varchar(50)
set @mypath = 'myserver.mydatabase.dbo.'

declare @SQL nvarchar(max)

set @SQL = 'select * from ' + @mypath + '[order]'

exec sp_executesql @SQL

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?