System Stored Procedures? - 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

SQL Server Development (2000)

System Stored Procedures?

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-11-10 : 18:15:26

I know how to create functions that reside in the master DB, but can be called in any database, but how can I create a stored procedure in the master DB and use it in any other database as if it were running from that DB?

Thanks,
Steve

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-10 : 18:18:09

USE MASTER

Create Procedure sp_MyProcedure

AS

blah

The sp_ part is what makes it available to other databases. When you execute a stored proc that starts with sp_ SQL server looks in the MASTER DB first. That is why you DON'T want to name your normal procs like that.

Damian

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-10 : 19:34:15

>> When you execute a stored proc that starts with sp_ SQL server looks in the MASTER DB first.
It will always look in master but if that is not a system sp it execute a version in the user database in preference.

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

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-11-11 : 08:14:44

Wow, and I was all ready to have to go through the process similar to that of registering a function.

Thanks guys.

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-11-11 : 08:36:25

Hmm...it appears I have encountered a problem. I created the SP in the master database, and it shows up there when I view it in EM. When I go to run it though, I get no results. However, I can run it from a different database, but specify that I want to list a stored procedure from the master DB and it will work just fine. Below is the code for the SP.


Create Procedure dbo.sp_generatedotnetparams
	(
	@ProcName sysname
	)

AS

SELECT ParamName = SUBSTRING(Parameter_Name,1,128)
FROM Information_Schema.Parameters
WHERE Specific_Name = @ProcName

Any ideas on what I'm doing wrong?

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-11-11 : 09:00:50

Turns out that sp_configure 'allow updates' did need to be called, in addition to sp_MS_marksystemobject. Below is the code for anyone needing to create a system procedure in the future.


USE master
GO
EXEC sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

If Exists(Select * From dbo.sysobjects Where id = object_id(N'dbo.sp_generatedotnetparams') And ObjectProperty(id, N'IsProcedure') = 1)
	Drop Procedure dbo.sp_generatedotnetparams
Go


Create Procedure dbo.sp_generatedotnetparams
	(
	@ProcName sysname
	)

AS

SELECT ParamName = SUBSTRING(Parameter_Name,1,128)
FROM Information_Schema.Parameters
WHERE Specific_Name=@ProcName

Go
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
EXEC sp_MS_marksystemobject 'dbo.sp_generatedotnetparams'
GO
EXEC sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources