adonetok (2/5/2013)
Below is statement for a tableselect * 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'
You have to use dynamic sql for this. You will also have to wrap your table name in [] because you are using reserved words as your object name.
declare @mypath varchar(50)
set @mypath = 'myserver.mydatabase.dbo.'
declare @SQL nvarchar(max)
set @SQL = 'select * from ' + @mypath + '[order]'
exec sp_executesql @SQL
I have a feeling this is the beginning of a generic stored procedure that will receive the table name as a parameter. This type of dynamic querying is a tell tale sign of that. This is not a good approach to stored procs. It will cause nothing but issues.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/