Can I have a single stored procedure that runs in multiple databases?
-
I have a number of databases with all the same table schemas and I am looking for a way of having just one stored procedure that can be run over each database. For example;
Database A has a table called "Employees"
Database B also has a table called "Employees"
Database C has a stored procedure called "sp_GetEmployees" that executes "SELECT * FROM Employees"
I would like to execute the SP from Database C but refer to data in Database A or Database B without having to maintain multiple copies of the procedure in each database.
Is this possible?
-
Sure u can use it....
for eg...
database_name.dbo.sp_GetEmployees will point to particular database Employee table...
i.e A.dbo.sp_GetEmployees -> point to A's Employee table...
I would add to other additional points...
1. Dont use 'sp_......' construct for user stored procedure... SqlServer identifies it as system SP and checks system table before checking user SP storage location which adds additional time for searching the SP.
2. U can execute SP's in another server database also... if its an linked server to ur server.... overthere u need to specify..
server_name_database_name.dbo.sp_name
Thanx
Sree
-
Sorry its,
'server_name.database_name.dbo.sp_name'
Typing mistake
-
I realise that you can call a stored proc from another database using dbname.owner.procedurename but what I want to do is call a stored proc and somehow specify in the parameters to the proc which database to extract the data from.
From my earlier example I would like something similar to,
USE C
GO
EXEC GetEmployees 'A'
to get the employees from database 'A' and
USE C
GO
EXEC GetEmployees 'B'
to get the employees from database 'B'
I know it would be easier to put all the data in the same database and use a flag to indicate which virtual database I refer to but I have inherited these databases from legacy applications that cannot be changed (the legacy app does not use Stored Procs)
-
yes, it is possible. in stored procedure u write the following code.
Select * from A.dbo.Employees if u want data from A database
Select * from B.dbo.Employees if u want data from B database
and if u want to show all data from A and B database then write the following code
Select * from A.dbo.Employees if u want data from A database
UNION
Select * from B.dbo.Employees if u want data from B database
execute the stored procedure in C database. u will get the result.u don't need to run this script in A or B databse.
Bye
Niladri
Thanks & Regards,Niladri Kumar Saha
-
If I understand you correctly, the following should work:
Create Procedure sp_GetEmployees
( @dbname varchar(50) )
as
declare @sql varchar(500)
set @sql = 'Select * From ' + @dbname + '.dbo.Employees'
EXEC (@sql)
go
To run procedure:
EXEC sp_GetEmployees 'databasename'
So to get data from A -- EXEC sp_GetEmployees 'A'
To get data from B -- EXEC sp_GetEmployees 'B'
Basically pass a parameter to the procedure, use the value passed to build a SQL string then use EXEC (sqlstring ) to return the result of the SQL string.
Cheers, Ian Scott
-
You could also create the stored procedure in the master database with the SP_ prefix and set it as a system object, that way regardless of where you ran it it would execute the select * from employees in the database doing the calling.
I'd also think about adding the with (nolock) hint too but that's dependant on whether the data is mostly static or fairly dynamic with lots of updates.
-
How do you "set it as a system object"?
I created a test stored procedure in master but I could not get it "see" the table of data in my calling database. (I note that the type is "User" and not "System" which may have something to do with my problem.)
Thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply