• 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'

    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/