August 24, 2004 at 11:23 am
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?
August 24, 2004 at 12:56 pm
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.
August 24, 2004 at 1:25 pm
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
August 25, 2004 at 6:25 am
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