Post reply

Pass table name to stored procedure2

  • July 5, 2005 at 7:01 am

    #88913

    Hi to all ,

    I know this subject allready been discussed , but I think my needs are different.

    I actually need to pass the server name and/or the database name as a parameter.

    I allready know the table name ...

     

    Thanks

    Yaniv

  • July 5, 2005 at 7:03 am

    #570848

    Make one proc per server and call the good server with an if/else.

  • July 5, 2005 at 7:12 am

    #570850

    But I don't know the server name nor the database name.

    these stored procedures supposed to be executed in any server

  • July 5, 2005 at 7:19 am

    #570851

    You'll have to use dynamic sql in this case. Make sure that the users cannot access those values in any way (other than picking the db from a combo).

    The Curse and Blessings of Dynamic SQL

  • July 5, 2005 at 7:39 am

    #570862

    Also bracket the values to stop injection, ie

    [databasename].[username].[tablename]

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • July 5, 2005 at 8:23 am

    #570876

    I don't get it if you don't know the server names you cannot have it as linked server. So ServerName.DataBaseName.dbo.ObjectName will not work.

    You should be knowing the database names in the server.

    CREATE PROC ExecMe

    (

    @pServerName VARCHAR(20),

    @pDatabaseName VARCHAR(20)

    )

    AS

    IF @pServerName = 'Server1'

    BEGIN

     IF @pDatabaseName = 'Db1'

      SELECT * FROM [SERVER1].[DB1].dbo.TableName

     

     IF @pDatabaseName = 'Db2'

      SELECT * FROM [SERVER1].[DB2].dbo.TableName

    END

    IF @pServerName = 'Server2'

    BEGIN

     IF @pDatabaseName = 'Db1'

      SELECT * FROM [SERVER2].[DB1].dbo.TableName

     

     IF @pDatabaseName = 'Db2'

      SELECT * FROM [SERVER2].[DB2].dbo.TableName

    END

    Regards,
    gova

  • July 5, 2005 at 9:15 am

    #570893

    And even with all the "if" statements if one of the linked servers to which the queries are referencing does not exists you won't be able to compile the above code! (the check is done apriori)

     

     

     


    * Noel

  • July 5, 2005 at 9:57 am

    #570907

    In that case you will need to use OPENDATASOURCE if the database is known or OPENROWSET if both server & database is parameterized

    Far away is close at hand in the images of elsewhere.
    Anon.

  • July 5, 2005 at 10:10 am

    #570915

    OR - as someone suggested above use dynamic sql

     


    * Noel

  • July 5, 2005 at 10:13 am

    #570918

    quoteOR - as someone  suggested above use dynamic sql

    Either/or, swings and roundabouts, all the fun of the fair

    Far away is close at hand in the images of elsewhere.
    Anon.

  • July 5, 2005 at 10:16 am

    #570921

    Who would dare to suggest such a thing ???

  • July 6, 2005 at 12:49 pm

    #571451

    dynamic SQL or not ...

    exec stored_procedure @@servername, db_name()

     

    gives you the present server name (whether a default or named instance) and the database name of you current 'context'.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • July 7, 2005 at 1:15 am

    #571656

    I have decided to go with dynamic sql

     

    Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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