USE DB as a variable

  • ftornoe

    SSC Rookie

    Points: 26

    Is it possible to set the database context to a variable?

  • Andy Warren

    SSC Guru

    Points: 119684

    Not sure what you're asking for. You could do this:

    declare @dbname varchar(128)

    set @dbname=db_name()

    Andy

  • ftornoe

    SSC Rookie

    Points: 26

    What I'm looking to do is supply the database name as a parameter to a stored procedure.

    for example:

    EXEC Procedure PUB

    In the procedure would be a variable like

    @DB

    Then I'd like to say

    USE @DB

  • Andy Warren

    SSC Guru

    Points: 119684

    I dont think so. You'd have to execute it as dynamic SQL using Exec().

    Andy

  • ftornoe

    SSC Rookie

    Points: 26

    Thanks. I've actually figured out a different means of getting what I need using another posting of yours. I really needed to get data into a variable in a stored procedure using data from a table in another database different than the one the procedure is logged into.

    Set @SQLStr =

    ('SELECT @MaxENSK = MAX(ENSK) FROM [' + @DB_ParmIn + '_Repository].[dbo].[tPrcdrENMRTN]')

    Set @ParmDefinition = ('@MaxENSK INT OUTPUT')

    PRINT @SQLStr

    Execute sp_executesql @SQLStr, @ParmDefinition, @EnumKey output

    PRINT CAST(@EnumKey as char)

    SET @EnumKey = @EnumKey + 1

    PRINT CAST(@EnumKey as char)

Viewing 5 posts - 1 through 5 (of 5 total)

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