does a stored procedure recognize its 'caller'

Question

does a stored procedure recognize its 'caller'

ragie

Mr or Mrs. 500

Points: 511
More actions
January 19, 2010 at 9:20 am

#219400

I was wondering if a stored procedure recognizes which process it was called from?
i.e. - Which ssis package it was called from?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Satya(SQLMaster) SSC Eights! Points: 872 More actions · Answer 1

Not really,it is just another database object. A stored procedure is a precompiled executable object that contains one or more Transact-SQL statements. Stored procedures can have input and output parameters and can also put out an integer return code. An application can enumerate available stored procedures by using catalog functions.

The stored procedures always run on the server, either the context of the server or of a database. In the case when SP is added by an assembly to the server or database context, any user can execute the stored procedure as long as the role for the user permits the actions performed by the stored procedure.

What is your requirement BTW?

-Satya SK Jayanty
SQL Server MVP (Follow me @sqlmaster)
Author of SQL Server 2008 R2 Administration CookBook
SQL Server Knowledge Sharing network

ragie Mr or Mrs. 500 Points: 511 More actions · Answer 2

Well, i have two SSIS packages that are quite similar to each other which needed to be split for various reasons.

However, in each package I call a stored procedure. The stored procedures are VERY similar to each other except for that they pull data from two separate tables. I thought that it would be very useful to place EVERYTHING in one sp, and i would do a 'check' in the beginning to see which process (ssis pkg) is calling the procedure and then execute the proper code.

something to this effect:

CREATE PROCEDURE dbo.StartProcess

CASE 'processCaller'

WHEN 'ProcessCaller' = PackageA THEN

INSERT/UPDATE/DELETE .....etc. etc.

FROM tbl1 WHERE processed = false

WHEN 'ProcessCaller' = PackageB THEN

INSERT/UPDATE/DELETE .....etc. etc.

FROM tbl2 WHERE processed = false

Lowell SSC Guru Points: 323518 More actions · Answer 3

can you have each process add a variable to the procedure call? or at least one of them? they can discover which process from a parameter, but not really from which process is calling it; they have access to some system variables, like host_name, @@spid, etc, but i'm not sure that helps.

CREATE PROCEDURE dbo.StartProcess(@ProcessSource varchar(30))

If @ProcessSource = 'PackageA '

BEGIN

INSERT/UPDATE/DELETE .....etc. etc.

FROM tbl1 WHERE processed = false

END

ELSE --blank @ProcessSource or not equal to 'PackageA '

BEGIN

INSERT/UPDATE/DELETE .....etc. etc.

FROM tbl2 WHERE processed = false

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ragie Mr or Mrs. 500 Points: 511 More actions · Answer 4

Lowell, this might be possible. thanks for guiding me in the right direction. I will look into this

ragie

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

In this case it would be better to keep the procedures separate. You may not get an efficient execution plan putting to two queries together in a single stored procedure.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

I agree with Lynn... while it's certainly OK to have more than one query in a stored procedure, conditional execution of two stored procedures isn't the optimal thing to do. Not sure it's possible to run such code without a recompile but the really bad part will be if an execution plan is reused that's geared toward one proc because of a recent execution when the other proc is what will be activated. Even dynamic SQL may be a better choice here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)