Accessing a dynamically chosen DB from a stroed proc without dynamic SQL?

Question

Accessing a dynamically chosen DB from a stroed proc without dynamic SQL?

Mark Rogers

Grasshopper

Points: 18
More actions
January 13, 2006 at 10:49 am

#90439

I am currently designning a system where the desired functionality is to be dynamically creating databases (on the same server) and then being able to access data in these databases from other stored procs in the main DB for the App.
As the stored procs will be large and complex I do not want to be dynamically generating every query.
I am looking for a way of either:
- Telling the procedure to divert all commands to the appropriate DB (like the USE statement but with variable)
- Specifying a variable at the start of the table name each time a table is mentioned in the SQL (like @dbname..tablename if it was allowed)
SQL Server 2005 tells me that the USE statement cannot be used inside a procedure and @dbname..tablename returns a syntax error.
Does anybody know a way in SQL Server 2005 of acheiving either of my two types of solution or a different solution to the same problem?
Thanks

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

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Mark,

you may script all stored procedures, put them in one SQL script and when dynamically generating a DB just run this script. Then the stored procedures will be local to the DB

Regards,Yelena Varsha

Marbry Hardin Old Hand Points: 331 More actions · Answer 2

You could create view(s) in the main DB that point to the dynamic DB(s) and just recreate the view with a reference to your new dynamic DB when that is created.

That way your stored proc references the view, and the code in the proc never has to change.

Or, if it becomes a performance issue, you could store the source of the stored proc with some kind of placeholders and insert code to reference your new DB directly when it is created. Then it's just a matter altering, or dropping and recreating your stored procedure.

Lowell SSC Guru Points: 323518 More actions · Answer 3

can you use an outer procedure to determine which database to use?

ie

CREATE procedure pr_dbSwitch as(@dbname) as

declare @SQL varchar(2000)

select @isql='USE @dbname pr_yourprocedure'

select @isql = replace(@isql,'@dbname',@dbname)

print @isql

exec(@isql)

that way your proc is not dynamic, but the way it is called for each db is?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mark Rogers Grasshopper Points: 18 More actions · Answer 4

Thanks for replies.

Yelana's solution would indeed work but as it is an ongoing project and there will be many databases added to the system this way keeping the procs up to date if changes are made would be a problem.

Marbry's views solution would work in a different case but in this case lots of the different DB's will need to be accessed by many different users at the same time.

It seems Lowells solution could be a good way to go I'll try it out.