October 9, 2007 at 7:04 am
Can anyone help me.
I have a stored procedure that puts information into a report table, lets call it tbl_report_information.
This stored procedure needs to get information from tables in another database.
The database this information comes from varies depending on the user calling it, but it must always end up in tbl_report_information.
What I was hoping to do was to pass in a paramter of the database name and use this to tell the stored procedure which table to pull information from ie
[@database].dbo.tbl_mydatabaseinformation
I don't want to have numerous copies of this stored procedure for each region that may call it - can this be done???
Any method of doing this would be appreciated.
October 9, 2007 at 7:47 am
You can't do this directly. The FROM clause requires resolution at compile time, so it can't take a variable.
You can run something like this:
declare @cmd varchar(200)
, @db varchar(20)
select @db = 'Northwind'
select @cmd = 'select * from ' + @db + '.dbo.customers'
exec(@cmd)
However you lose some efficiencies for execution, compiled plans, etc. How many databases are we talking about? Your time as a developer might mean much less than that for the users penalty for execution. It might be better to just write a dozen stored procedures.
October 9, 2007 at 1:48 pm
Thanks, I had found similar information myself. It seems strange that you have to have a dozen stored procedures to do much the same thing in every database. The stored procedure is too long and complicated to faff about joining it all into a variable. I'll just have to have numerous copies!
October 9, 2007 at 2:05 pm
Well - that's what Steve is getting at - you don't HAVE to have a bunch of stored procedures if you're okay when them running less than ideally. SQL does a fair amount of optimization if you give it a chance to, and dynamic SQL is something that's "hard to optimize".
It all depends on what the SP does. If it its a lot of data, big joins, etc... (i.e. a big "data" query), you'd probably be best off having each DB have its own copy. If it's "complicated" in some way other than data-intense, or if you can live with the performance you get out of the dynamic SQL option, then perhaps keep one SP.
If you ARE going to go down the dynamic SQL route, I'd advise trying to set the stored procedure with the WITH RECOMPILE option, so that SQL Server will always try to make sure it is using the best way for the database it's calling.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy