EXEC a stored proc for selected rows

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

Transact-SQL (2000)

EXEC a stored proc for selected rows

Author

Topic

DLaRue
Starting Member

7 Posts

Posted - 2002-10-02 : 11:12:22

This is a simplified version of a question I asked the experts. That was before I figured out the message board here...

Anyway I want to EXEC a stored proc for selected rows in a table. But I can't figure out a clean syntax to do it. There has to be a better way to do this:

/* this is the original procedure */
create procedure pA (@p1 int, @p2 int)
as
update tA
set f1 = @p1
where f2 = @p2 /* this can select more than one row in tA */

/* I want to do something like this */
create procedure pA (@p1 int, @p2 int)
as
update tA
set f1 = @p1
exec pB @p1, f2, f3 /* illegal */
where f2 = @p2

/* I've only found this to work */
create procedure pA (@p1 int, @p2 int)
as
declare c1 cursor local
for select f1, f2, f3
from tA
where f2 = @p2
open c1
declare @f1 int, @f2 int, @f3 int
fetch next from c1 into @f1, @f2, @f3
while (@@FETCH_STATUS = 0)
begin
exec pB @p1, @f2, @f3
fetch next from c1 into @f1, @f2, @f3
end
close c1
/* here's hoping that the rowset where f2 = @p2 hasn't changed! */
update tA
set f1 = @p1
exec pB @p1, f2, f3
where f2 = @p2

rharmon
Starting Member

41 Posts

Posted - 2002-10-02 : 11:28:09

I think I may idea, what does the code in Pb look like?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-02 : 11:43:23

Do you want pB to act on each of the f2, f3 values from tA then update tA with the result?
if so no way round calling it individually for each row.
You could probably code whatever you have in pB into an update statement in this sp though.
If you want it to set f2 and f3 though you can use a function.

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

DLaRue
Starting Member

7 Posts

Posted - 2002-10-02 : 11:49:39

I'm hoping for a generic solution because pB can vary. This is a simplified example:

create procedure pB (@p1 int, @p2 int, @p3 int)
as
declare @t1 int
set @t1 = case @p1
when 1 then 3
when 2 then 277
when 3 then 33
else 42
end
insert into tB (f1, f2, f3, f4, f5, f6)
values (@t1, @p1, @p2, @p3, 2, 0)

DLaRue
Starting Member

7 Posts

Posted - 2002-10-02 : 11:54:08

There are about a dozen stored procs like pA where I want to call pB. The logic in pB is fairly complex in real life so I want to keep it in one place and not have the duplicate code floating around.

DLaRue
Starting Member

7 Posts

Posted - 2002-10-02 : 12:02:02

The subtle gotcha is at the comment:

/* here's hoping that the rowset where f2 = @p2 hasn't changed! */

I want to make sure that pB is called for the exact, same rows in tA that are updated in pA.

rharmon
Starting Member

41 Posts

Posted - 2002-10-02 : 12:05:14

About the only thing I can come up with after playing with it for a minute is to re-create the SPs as User Defined Functions as an SP cannot be used in a scalar maner. I was trying very hard to call the sp from within a UDF, but it's not allowed. When I try it through dynamic sql (trying to fake out the code parser) it gives me an "illegal use of execute"

Sorry

ROb

DLaRue
Starting Member

7 Posts

Posted - 2002-10-02 : 12:23:07

User defined functions aren't allowed to have permanent side effects (like inserting rows into a non-temporary table) so I can't put the functionality of pB into one.

The UDFs also have another serious (for me) draw back: they have to be called with a two-part name as in: owner.udfB
Once it hits production I don't have control over the owner name. So I have to stick with a stored procedure solution.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-02 : 12:27:18

In a production system, everything should be owned by dbo, so all UDF's would be called as dbo.MyFuncName. If you're not doing this now, you really need to start. It will save you a TON of problems later. Functions can always be tested under another owner name but should always be finally deployed by the dbo.

DLaRue
Starting Member

7 Posts

Posted - 2002-10-02 : 13:06:29

I am NOT an expert on this stuff, just learning as I go. However, our productions systems don't use dbo as the owner for stored procs and the like. I'm not totally sure of their reasoning, but I can think of a few guesses, mostly related to granularity of security. Yes, it's more complicated. But control, responsibility, delegation, and security needs have to be balanced when working with large systems.

DLaRue
Starting Member

7 Posts

Posted - 2002-10-03 : 09:28:21

Well, it looks like my boss hates cursors (too expensive) more than triggers. So an update trigger will be the way to go.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources