Re: Stored procedure, which table is used ?

From: ben brugman (ben_at_niethier.nl)
Date: 02/20/04 

Date: Fri, 20 Feb 2004 09:20:49 +0100

> >CREATE PROCEDURE sp_demo AS
> >select * from demotable
> >exec ('select * from demotable')
> >GO

The first select takes the table from Master.
The second select (in the exec) takes the table from the
environment were it is run.
(See the example at the end of my original message.)

ben brugman

"chris" <anonymous@discussions.microsoft.com> wrote in message
news:1260b01c3f701$28bbf360$a101280a@phx.gbl...
> >Which table does a stored procedure use when
> >run in the Query analyser under local database.
>
> Whichever table the proc calls.
>
> I may not be following here. All your queries and all your
> procs will run in the local db by default. The exception
> to this would proably be some of the "sp_"'s which will
> probably be run in Master even if called from a user db.
>
>
>
>
> >-----Original Message-----
> >A stored procedure in the master database.
> >
> >Which table does a stored procedure use when
> >run in the Query analyser under local database.
> >If I do a 'normal' select from, it tries to read the
> >table form master.
> >During an exec the table comes from local database.
> >
> >Why the difference ?
> >
> >What should I do to 'force' the 'local' table from
> database ?
> >
> >Using an exec around all my queries is less elegant.
> >(more confusing and error prone).
> >Is there a design that I am missing ?
> >
> >ben brugman
> >
> >
> >RESULTS FROM HERE :
> >
> >OKKI
> >------
> >Master
> >
> >(1 row(s) affected)
> >
> >OKKI
> >----------
> >LocalDatabase
> >
> >THE DEMO CODE :
> >
> >-- In the master database.
> >use master
> >go
> >CREATE PROCEDURE sp_demo AS
> >select * from demotable
> >exec ('select * from demotable')
> >GO
> >
> >
> >-- In the master database.
> >use master
> >select 'Master' as OKKI INTO DEMOTABLE
> >
> >
> >-- In another database.
> >use LocalDatabase
> >select 'LocalDatabase' as OKKI INTO DEMOTABLE
> >
> >--In the query analyser run the sp_demo under
> >--the another database.
> >--
> >--
> >print 'RESULTS FROM HERE :'
> >use LocalDatabase
> >
> >exec sp_demo
> >
> >use LocalDatabase
> >drop table demotable
> >
> >use master
> >drop table demotable
> >drop procedure sp_demo
> >
> >
> >.
> >

Relevant Pages

  • 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)
  • Stored procedure, which table is used ?
    ... >run in the Query analyser under local database. ... probably be run in Master even if called from a user db. ... >A stored procedure in the master database. ... >select 'LocalDatabase' as OKKI INTO DEMOTABLE ...
    (microsoft.public.sqlserver.server)
  • 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: Stored procedure, which table is used ?
    ... > risk etc etc. ... Is there a reasoning behind the local versus master table use? ... >> run in the Query analyser under local database. ... >> use LocalDatabase ...
    (microsoft.public.sqlserver.server)
Loading