one stored proc used in many databases 2

[rac2]

I have several databases, all with the same structure and all on the same SQL Server. And I have a stored procedure that can be used with any one of the databases. The database to be used is specified in a web page. The stored procedure creates a temporary table, builds the temporary table in a WHILE loop, in the loop a table is queried with conditions that depend on the loop counter, and finally it SELECTS the rows from the temporary table.

How can I use this procedure? Should it be a stored procedure? I want the procedure to be in one place and to run it with a variable for the database name.

 

[tlbroadbent]

A stored procedure created in the master database with a name that starts with "sp_" is called a system stored procedure and can be executed from any database within the context of that database. That is how SPs like sp_help, sp_tables, sp_adduser, etc. can be executed in every database. Create your SP in master with "sp_" as the first three characters of the name.

Use yourdatabase
exec sp_yourprocedure ... Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[rac2]

Thank you for the explanation.

But I must be missing something. I find that I can execute the SP from any database, but the result is always for the data in master.

Here is a procedure sp_CountActivePlans which is in master.

DECLARE @asOfDate DATETIME
DECLARE @numberActive INT

SET @asOfDate = '01/01/2002'


SELECT @numberActive =  COUNT (PlanID)
FROM plans
WHERE
	(
		( ServiceStart < @asOfDate
		  AND ServiceEnd IS NULL )
	OR
		( ServiceStart < @asOfDate
		   AND ServiceEnd >  @asOfDate)
	)

SELECT @numberActive

I run the SP and the procedure code in Query Analyzer.

USE anyDatabase
EXECUTE sp_CountActivePlans

and also the code above.

The results for sp_CountActivePlans are for data in master, while the results for the code are for the database I specifiy in the USE statement.

What have I missed?

 

[tlbroadbent]

I apologize for leaving out a step. Run the following command to mark the new SP as a system SP.

use master
go
EXEC sp_MS_marksystemobject sp_CountActivePlans
go

Let me know if it works for you. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[wofbox]

rac2:
If I correctly understood your idea, looking at your sp code I belive that you should have to "full-qualify" the names of the objects involved that are external to the db the SP is allocated. That is :

where you used
...SELECT @numberActive = COUNT (PlanID)
FROM plans

i belive you should use :

...SELECT @numberActive = COUNT (PlanID)
FROM targetdatabase..plans

off course, assuming you need a general solution, you should pass the "targetdatabase" as a parameter to the SP

Hope it will be usefull,

Walter

 

[rac2]

Yes. Marking the SP as a system object makes it use whatever database I choose.

Thank you very much.

 

[rac2]

Hello Walter,

Your method also works. Here is my code for passing a parameter. The stored procedure can be in an arbitrary database and does not need the special name beginning with sp_ .

In this particular case where I want to use the @numberActive variable elsewhere in the stored procedure it gets a little messy. I must put all of the statements involving @numberActive into a string and use EXEC().

Unless there is another way to pass the database name without building a string?

CREATE PROCEDURE [proc_CountActivePlans_WITH_PARM] (
	@rDBName CHAR(3)
)

AS

DECLARE @asOfDate CHAR(10)
DECLARE @sqlStatement VARCHAR(255)


SET @asOfDate = '01/01/2002'


SET @sqlStatement = "DECLARE @numberActive INT; "
SET @sqlStatement = @sqlStatement + "SELECT @numberActive =  COUNT (PlanID) FROM "

SET @sqlStatement = @sqlStatement + @rDBName + "..plans "

SET @sqlStatement = @sqlStatement + &quot;WHERE (( ServiceStart < '&quot; + @asOfDate
SET @sqlStatement = @sqlStatement + &quot;' AND ServiceEnd IS NULL ) OR ( ServiceStart < '&quot; + @asOfDate 
SET @sqlStatement = @sqlStatement + &quot;' AND ServiceEnd > '&quot; + @asOfDate + &quot;')); &quot;
SET @sqlStatement = @sqlStatement + &quot;SELECT @numberActive&quot;

EXEC ( @sqlStatement )

And to run the SP in DB1 from another database DB2 -

EXECUTE DB1..proc_CountActivePlans_WITH_PARM 'DB2'

Thank you too for thinking about my problem.