Database Reference - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Database Reference

Author

Topic

shafiqm
Starting Member

9 Posts

Posted - 2004-03-03 : 10:11:01

HI,

How I can dynamically set the database reference

e.g

Use Northodwind

Instead of this

DECLARE @Database
Use @Database

where @database is passed as parameter. How I can do that in SQL?
Thanks
Shafiq

sphadke
Yak Posting Veteran

55 Posts

Posted - 2004-03-03 : 10:52:58

Where are you trying to use this?? is it for setting a default database for a user?

you can always use this in QA
USE NorthWind
GO

Sachin

shafiqm
Starting Member

9 Posts

Posted - 2004-03-03 : 12:02:03

No It's in a stored procedure but the database name will be passed as parameter.

As same table exists in multiple databases.

Thanks
Shafiq

sphadke
Yak Posting Veteran

55 Posts

Posted - 2004-03-03 : 12:06:42

You cannot use the USE DatabaseName syntax in a stored procedure however you can use this

SELECT * FROM databasename..tablename to reference to your DB/Table

Sachin

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-03 : 12:24:43

If you pass the db name as a parameter then you will have to do everything in dynamic sql

select @sql = 'select * from ' + @dbname + '..mytbl'
exec(@sql)
or maybe easier
select @sql = exec ' + @dbname + '..sp_executesql N''select * from mytbl'''
exec(@sql)

But then there's not much point in using stored procs.
Better to have a copy of the SPs in the database and call that - maybe using a shell SP which just calls the SP in that db.

==========================================
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.

shafiqm
Starting Member

9 Posts

Posted - 2004-03-03 : 15:40:30

Thanks for the replies. I am aware of building a dynamic SQL but as the SQL is very long and do not want to concatenate to create a SQL string.

Here is the scanario.
Payroll has an yearly database.
Current Year : Payroll
2003 Database: Payroll_2003
2002 Database: Payroll_2002

I need to give my user a query to return all terminated employees belonging to this year and the one previous year. Stored Procedure is always in the current year database but to find the previous year database I need to find the exact database name.
If I donot used the dynamic string then I have to change the stored procedure every year.

Is there any other alternatives to the dynamic string building?

Thanks
Shafiq

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-03 : 15:50:55

No there aren't any alternatives.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources