Re: Creating System SPROC

From: Uri Dimant (urid_at_iscar.co.il)
Date: 07/06/04 

Date: Tue, 6 Jul 2004 08:20:37 +0200

Scott
Look at this example
use master
create table t(c1 varchar(50)) insert t values('master')
go
create proc sp_test as select * from t
GO
use northwind
create table t(c1 varchar(50)) insert t values('northwind')
use pubs
create table t(c1 varchar(50)) insert t values('pubs')
use pubs
exec sp_test --returns 'master'
use master
exec sp_MS_marksystemobject sp_test
use pubs
exec sp_test --returns 'pubs'
use northwind
exec sp_test --returns 'northwind'

"Scott" <sbailey@mileslumber.com> wrote in message
news:%23YYzLvuYEHA.3128@TK2MSFTNGP09.phx.gbl...
> Could you tell me what type of variable to add for the database name if I
do
> use in MASTER? I created a special WeekNumber function that several
> databases use. I'm trying to keep it down to as few lines as possible
> because it's pretty long and complex now.
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> news:Oq7n0nuYEHA.2972@tk2msftngp13.phx.gbl...
> > Ooh, don't let Celko get a hold of this one. ;-)
> >
> > Personally, I think you should create a copy of the stored proc in each
> user
> > database. (Create it in model so that all new databases will have a
> copy.)
> > Also, use the dbo. prefix...
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> >
> > "Scott" <sbailey@mileslumber.com> wrote in message
> > news:%23tjnVduYEHA.3536@TK2MSFTNGP11.phx.gbl...
> > >I have a simple SPROC below that I use inside a database. I pass a
table
> > >and
> > > field and it returns the max date in the table.
> > >
> > > I'd like to put it in my MASTER database, so I can call it to return
max
> > > dates in several databses on my server. I know I need to add a
variable
> > > for
> > > the database name. I've never written a system SPROC before, anyone
care
> > > to
> > > help me modify the code to check the database I pass variable for?
> > >
> > >
> > > CREATE PROCEDURE GetMaxDate
> > > @sTable Char(40) Output,
> > > @sField Char(40) Output
> > > AS
> > > EXEC ("SELECT Max(" + @sField + ") FROM " + @sTable)
> > > GO
> >
> >
>
>

Relevant Pages

  • Re: System sproc to channel to database(s)
    ... permissions in master. ... use northwind ... use pubs ... exec dbo.sp_MS_marksystemobject 'dbo.sp_insertSomething' ...
    (microsoft.public.sqlserver.programming)
  • Re: Two Way Bidirectional Rep
    ... Let me post my entire script with what I did. ... use master ... exec sp_replicationdboption N'WEB_TSK_MGMT', N'publish', true ... "The process could not connect to server 'GRSSQL'. ...
    (microsoft.public.sqlserver.replication)
  • Re: Granting EXEC to all my user sprocs in one hit
    ... You can run the procedure below (after you have created it in master) in the ... Grant the EXEC privilege to these user-sprocs only (excluding the ... > I'm detaching the live copy and copying the files across to dev machine, ... > set the permissions. ...
    (microsoft.public.sqlserver.security)
  • Re: User can see and access Master and Northwind DB
    ... This is because of the guest user account in master and Northwind ... > I'm new to SQL so here is my dilema. ...
    (microsoft.public.sqlserver.security)
  • Re: stored proc question
    ... use northwind ... exec sp_test --returns 'master' ... > As I was implementing a sproc to one of my database, ...
    (microsoft.public.sqlserver.server)