SP calls another SP - 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 2005 Forums

Transact-SQL (2005)

SP calls another SP

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-09-01 : 09:06:02

can i , when 1 Stored Procedure 1 (sp1) calls to SP2
then in sp2 to know the name of the SP that called it,
withoguht the needto pass the calling name SP to SP2

thnak in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Kristen
Test

22859 Posts

Posted - 2007-09-01 : 09:32:35

Can't think of a way, other than possibly interrogating the SPID in some way ... which, if possible, would probably be a ghastly hack!

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-03 : 16:52:47

why make life more difficult? is this part of some sort of audit? Or are you going to use it as a condition inside sp2? just pass the name over to sp2

keep it simple.

--------------------
keeping it simple...

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-09-07 : 10:49:33

k

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-09-07 : 10:58:54

Throwing good money after bad ....

Create a temp table in sp1:

create table #WhosYourDaddy(procname sysname)
insert into #WhosYourDaddy select 'sp1'

Then query that temp table in sp2, since it should still be in scope ...

Jay
to here knows when

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 11:04:22

best way to go with this is
for your sp2 to have a parameter
@calledBy int

then you can call your sp2 like this
exec sp2 <yourParams>, @calledBy = @@PROCID

@@PROCID is the id of the current module (can be used in stored procedure, trigger or user defined function)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-07 : 13:43:58

excellent! that's very very nice indeed Mladen

--------------------
keeping it simple...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 13:47:16

i know

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-09-07 : 14:35:02

nice
from which tables can i get data of : @@PROCID

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 14:37:26

@@PROCID is a global variable
select @@PROCID

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-09-07 : 14:44:06

ok

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources