Store procedure variable

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

  • what error?

    declare @mypath varchar(50), @sql nvarchar(100)

    set @mypath = 'myserver.mydatabase.dbo.'

    set @sql = 'select * from ' + @mypath + 'order'

    sp_executesql @sql

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

  • Error is:

    Msg 102, Level 15, State 1, Line 4

    Incorrect syntax near '+'.

    But if I run your script (ad @sql), it is ok

  • Because the query is dynamic, and without sp_executesql it will not expand the contents of the variable.

    Dynamic queries like this are bad practice, I would highly recommend not doing this.

  • The purpose to use it is that I need to create a store procedure to read one Order table. Based on server status, sometime user need to change server name from application. The store procedure should be something like below so that user can switch server name by passing parameter @dailytablepath

    CREATE PROCEDURE InitList

    @dailytablepath varchar(20)

    AS

    BEGIN

    SET NOCOUNT ON;

    SELECT *

    FROM @dailytablepath + '[ORDER]'

    END

  • The code I posted will accomplish this.

    _______________________________________________________________

    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/

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

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

  • If serverA is down, user can switch to backup serverB.

    The store procedure is store in serverA

  • If the proc is in A and A is down, then they wont be able to run the proc as they cant get to A, so your logic is flawed.

    What high availability routines do you have in place?

  • Sorry, it is my fault. I did not make clear.

    What I mean is [Order] table in serverA is not correct so that user need to use [Order] table in serverB.

  • Well then as long as you have a linked server from ServerA to ServerB you will be ok.

    But I seriously would recommend changing the connection string and not using dynamic SQL.

  • anthony.green (2/5/2013)


    Well then as long as you have a linked server from ServerA to ServerB you will be ok.

    But I seriously would recommend changing the connection string and not using dynamic SQL.

    +1

    _______________________________________________________________

    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/

  • Is dynamic SQL bad in general or just when it opens up this sort of a security hole allowing for the SQL to fully specified or at least the table?

    Time to make a change

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply