Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Germany
    Posts
    17

    Unanswered: Setting a @db variable

    Hi, I have this query basically im trying to set the @db to the current database depending on the quarter. This will be used in a package to change the database that the package will use.

    DECLARE @db nVarchar(4000)
    SET @db = N'TESTDB' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
    THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
    THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10')
    THEN 'Q4' END END END END
    EXECUTE sp_executesql @db, N'@level tinyint', @level = 35

    I get the error Incorrect Syntax near 'TESTDB04_Q3'.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You're trying to "execute" your database name.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a swag, but:
    Code:
    DECLARE @db NVARCHAR(100)
    
    SET @db = N'TESTDB'
    +  CASE 
          WHEN 1 = Month(GetDate())		THEN Convert(CHAR(2), DateAdd(year, -1, GetDate()), 12) + '_Q4'
          WHEN Month(GetDate()) IN (2, 3,  4)	THEN Convert(CHAR(2), GetDate(), 12) + '_Q1'
          WHEN Month(GetDate()) IN (5, 6,  7)	THEN Convert(CHAR(2), GetDate(), 12) + '_Q2'
          WHEN Month(GetDate()) IN (8, 9, 10)	THEN Convert(CHAR(2), GetDate(), 12) + '_Q3'
          WHEN Month(GetDate()) IN (  11, 12)	THEN Convert(CHAR(2), GetDate(), 12) + '_Q4'
       END
    
    EXECUTE ('USE ' + @cDb)
    -PatP

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    But after you EXEC your statement, the scope will pop right back into your prior database. You will only be in the target database for the duration of the EXECUTE procedure. This means that ALL your code will need to be dynamic sql appended to your SET DATABASE string and run at a single shot.

    It's a messy way to do things, and should be avoided if possible.

    What about linking these database tables into a common database?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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