Using parameter for different databases

  • Hi

    We need a select statement in a stored procedure. This sp is called from a application but the source can be different databases.

    @environment as varchar(8)

    select va. codart, l.libel1 from @environment.dbo.vficart va

    join location l

    on va.codart = l.code_art


    We receive an error on a '.' probably the one before dbo.

    N E suggestions ?



  • Check this out for a better way out :

    Dynamic database

  • Why not have the SP on all the different servers that may get called.  THEN set-up your different connections inside your WEB application and call the proper connection for whichever server you need.  This way you avoid DYNAMIC SQL...

    Good Hunting!

    AJ Ahrens

  • Here's the "one step better" I was missing

    thanx aj

  • Hey thx for yr replies.

    It seems that dynamic sql is dangerous 😉

    But as we often have to create new databases, we don't want to have a list to do's when setting it up. So when it's dynamical build, the less worries we have.

    That one article seems interesting but doesn't point out how to use different databases.


  • yes it does... but it's very easy to miss.

    But as you pointed out this is something to avoid if at all possible :

    Get data from another database. If you for some reason have your application spread over two databases, you should not litter your code with hard-coded references with database names, because the day want you a test environment on the same server, you will have a problem. So an idea is to get the name of the other database from a configuration table, and build a dynamic SQL statement. There is however an alternative here, presuming that you can confine the operation on the other database in a procedure on its own. In such case you can do things like:

    SET @sp-2 = @dbname + '..some_sp'

    EXEC @ret = @sp-2 @par1, @par2...

  • Hey Journeyman,

    Check out this stored proc:

    create procedure dbo.usp_getData


     @environment nvarchar(100)




     set @environment = ltrim(rtrim(@environment))

     declare @errmsg as varchar(255)

     if ( isnull(@environment,'') = '' )


      set @errmsg = 'No database provided'

      raiserror 130000 @errmsg

      return (1)


     -- check whether @environment is a valid database

     if ( db_id(@environment) is null )


      set @errmsg = 'Database ' + @environment + ' is not a valid database.'

      raiserror 130000 @errmsg

      return (1)


     declare @sql as nvarchar(1000) 

     set @sql = N'select va. codart, l.libel1 from ' + @environment + N'..vficart va join location l on va.codart = l.code_art'

     exec sp_executesql @sql





  • This would seem like a safe way to use dynamic sql.... but I would still use it to call a stored proc in the database... that way you get the advantage of a saved execution plan.

Viewing 8 posts - 1 through 7 (of 7 total)

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