get calling database name

  • I'm having trouble getting the current database name when calling a stored procedure. My stored procedure drops tables in the database when the name of the table is a match. I do not want to have to pass the current database name into the stored procedure. The problem is happening where I'm calling the stored procedure to do this in a database that is different from the database that has the tables I want to drop.

    I've tried using  set @d_name = (SELECT DB_NAME()) within my stored procedure to get the name of the current database, but it keeps getting the name of the database the stored procedure is in. Any ideas?

  • set @d_name = dbname

    I think dbname will work in a transaction, but won't as a simple select.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • Hi Jennifer,

     

    I has the some trouble yesterday. I try use db_name() in all forms but allways return the same thing, the database the stored procedure is in.

    I solve this passing the database name as parameter

    e.g.

    sp_mysp db_name()

    or

    sp_mysp 'mydatabase'

     

    in my sp I wrote:

     

    create sp_mysp ( @dbname sysname = null )

    set @dbname = isnull(@dbname, db_name())

    ...

    declare @cmd nvarchar (500)

    set @cmd = 'select * from ' + @dnmame + '..mytable '

    ...

     

    This is the only way I found.

    If anyone has another solution I thanks too

     

  • Jennifer,

    Unfortunately, the only way I know of to accomplish what you want is to create the stored procedure in the master database, and begin the SP's name with 'sp_'. Then call the SP from any database, and DB_NAME() will return the proper database name. The problem is that you can't reference objects (without qualification) that don't exist in master. It all depends on your specific SP code.

    EXAMPLE:

    If I have a database called TEST, I can test this with the following code:

    use master

    -- drop procedure sp_MyProc

    go

    create procedure sp_MyProc

    as

      print db_name()

    go

    exec sp_MyProc                   -- displays: master

    use TEST

    exec sp_MyProc                   -- displays: TEST

    exec master..sp_MyProc        -- displays: master

Viewing 4 posts - 1 through 3 (of 3 total)

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