Post reply

USE DB with a variable

  • January 4, 2007 at 3:29 pm

    #176241

    Is it possible to dynamically set the database with variable?

    I tried...

    Declare @db varchar(15)

    set @db = 'Databasename'

    USE @db

    I got a syntax error.  Can this be done?  What am I missing?

  • January 4, 2007 at 3:46 pm

    #681349

    Execute this from a database other than Northwind.

    DECLARE @DBName varchar(100), @sqlStr varchar(1000)

    SET @DBName = 'NorthWind'

    SET @sqlstr = 'USE ' + @DBName + ';'

    SET @sqlstr = @sqlstr + 'SELECT COUNT(*) FROM Products'

    EXEC(@sqlstr)

  • January 4, 2007 at 11:14 pm

    #681387

    Be aware that the Use statement will only apply to the dynamic query being run.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • January 5, 2007 at 3:26 am

    #681422

    While on the topic, sometimes you want to know what database you're in:

    declare @dbname varchar(30)

    set @dbname=(select d.name

                 from master.dbo.sysprocesses p,master.dbo.sysdatabases d

                 where p.spid=@@spid and p.dbid=d.dbid)

  • January 5, 2007 at 3:29 am

    #681423

    What's wrong with select db_name() ..?

    /Kenneth

  • January 5, 2007 at 3:36 am

    #681425

    Indeed! The BOL doesn't say that the dbid parameter is optional. And I never thought about just leaving it out to get at the current db name. Thus my convoluted solution.

  • January 5, 2007 at 3:39 am

    #681427

    It also works should you dare dabble with dynamic SQL.

    use tempdb

    go

    declare @sql varchar(255)

    set @sql = 'use master select db_name()'

    exec ( @sql)

    go

    /Kenneth

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

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