Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002
    Posts
    192

    Unanswered: Variable name in place of Database name

    I would like to do the following..

    Create Procedure spTest

    @CatalogName NVARCHAR(100)

    SELECT * FROM @CatalogName.DBO.TestTable

    Go

    But, it doesn't work because SQL Server does not recognize @CatalogName as a variable name when used in that context. Is there any way to substitute the Catalog or database name with a variable?

  2. #2
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    you can declare a variable @vSQL (name is just an example, can be anything) of type varchar.
    SET @vSQL = 'SELECT * FROM ' + @CatalogName + '.DBO.TestTable'
    EXEC (@vSQL)

    This should do the trick
    Johan

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Check BOL for sp_executesql topic also.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Mar 2002
    Posts
    192
    Thanks for the tip. It worked.

    Originally posted by jora
    you can declare a variable @vSQL (name is just an example, can be anything) of type varchar.
    SET @vSQL = 'SELECT * FROM ' + @CatalogName + '.DBO.TestTable'
    EXEC (@vSQL)

    This should do the trick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •