Manipulating SP within a 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 2000 Forums

Transact-SQL (2000)

Manipulating SP within a SP

Author

Topic

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-08-28 : 03:02:56

Hi,
I want to update/Insert the db and I want to use only 2 SP for that.
My plan is to use a 'general' SP that will call 'GetProduct' SP each time and from the answer of the 'GetProduct' will deside if to update or insert the record.
What should the 'GetProduct' return and what is the manipulation I should do in the general SP?
Till now I called each time the GetProduct SP that returned a recordset, in the application i checked if the record exist and called update or Insert SP. I want to avoid calling twice the db.
Please advice.
Thanks
Yossi
Yossi

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-28 : 03:14:58

create procedure UpdateProduct
@name ,
@group ,
.....

as
-- name, group = PK
if exists (select * from product where name = @name and group = @group)
begin
update product
set ...
where name = @name
and group = @group
end
else
begin
insert product name , group , ...
select @name, @group, ...
end

go

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

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-08-28 : 04:24:00

Thanks a lot.
Yossi

quote:

create procedure UpdateProduct
@name ,
@group ,
.....

as
-- name, group = PK
if exists (select * from product where name = @name and group = @group)
begin
update product
set ...
where name = @name
and group = @group
end
else
begin
insert product name , group , ...
select @name, @group, ...
end

go

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

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-08-28 : 04:32:05

Hi again,
Can I retrieve a recordset in SP A from SP B and run some kind of while rs.EOF loop inside SP A? the rs is the recordset retrieved from SP B.
All that to avoid multiple calls to the SP from the application.
Thanks again
Yossi

quote:

create procedure UpdateProduct
@name ,
@group ,
.....

as
-- name, group = PK
if exists (select * from product where name = @name and group = @group)
begin
update product
set ...
where name = @name
and group = @group
end
else
begin
insert product name , group , ...
select @name, @group, ...
end

go

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

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-28 : 04:51:55

What are you trying to do?

You can crate a temp table with an identity in spA - populate it with the records call spB which loops through the identity column to process row by row.

or spB can do
insert #recs exec spA to populate the temp table itself.

If all you want to do is update / insert you don't need to do it one rec at a time though.
I think I answered a question about this a couple of days ago and gave the process.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources