Using database name in a stored proc

[tonykblen]

Hi,

I have a stored procedure that updates a single record. Instead of me having to pass in a parameter with the database name, I want to use the stored proc to rename any database with the following syntax:

sqlserver-*The name of the database*

Currently I hard-code the first part of the value and prompt the user to supply the second, which should not be necessary as all that needs to be supplied is the name of the database e.g.

sqlserver-uat
sqlserver-live

This is the procedure I currently have:

CREATE PROCEDURE dbo.changedbname
(
@dbname varchar(80)
)
AS
Select * from zxsystem where zxg_property= 'dbid'
Update zxsystem set zxg_value= 'sqlserver-' + @dbname where zxg_property='dbid'
Select * from zxsystem where zxg_property= 'dbid'
GO

Any help/advice would be greatly appreciated,

Tony.

Tony Kennedy BSc. B.I.S.,
MCSA Cand.

A good start is half the work.
Every start is difficult .
-Two Gaelic proverbs

 

[nigelrivett]

Not sure what you mean.
If you want the current database name this can be obtained from db_name()

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[tonykblen]

Hi Nigel

Many thanks for that. That has done the trick! The procedure now is:

CREATE PROCEDURE dbo.changedbname
(
@dbname varchar(80)
)
AS
Select * from zxsystem where zxg_property= 'dbid'
Update zxsystem set zxg_value= 'sqlserver-' + db_name()where zxg_property='dbid'
Select * from zxsystem where zxg_property= 'dbid'
GO

Cheers again,

Tony.


Tony Kennedy BSc. B.I.S.,
MCSA Cand.

A good start is half the work.
Every start is difficult .
-Two Gaelic proverbs