Link or example of calling SP from within a SP

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)

Link or example of calling SP from within a SP

Author

Topic

Sirrombor
Starting Member

12 Posts

Posted - 2003-08-04 : 14:01:50

Hi,

I have a stored proc that is called from a web script. I need to pass some parameters into it for inserting, return the Identity and then pass the identity value and some other params to another stored proc for inserting.

Can someone give me an example or link to where I can learn how to accomplish this?

Here is an example of what I am trying...

ALTER PROCEDURE dbo.sp_NewPosting

@PostSubject nvarchar (256),
@CustNumber varchar (1000),
@UserID int,
@CustQuestion ntext,

AS

INSERT INTO dbo.Tbl_Postings
(PostSubject,
CustNumber,
UserID,
CustPostDate)

VALUES(@PostSubject,
@CustNumber,
@UserID
GETDATE())

SET @PostID = @@IDENTITY

/* Above works can't figure out how to then fire off another SP and pass it values...*/

EXEC sp_NewQuestion @PostID, @CustQuestion, @UserID

/* I need to get the value of @PostID and pass it to the other proc along with the rest of the Web Page form values.*/

/* I would have done an additional insert but I want a separate stand alone proc that I can call alone for another piece of the site.*/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-04 : 14:14:24

What is the error that you are getting?

It worked fine for me when I ran your code (modified to fit my sample tables and stored procedures).

CREATE PROCEDURE dbo.sp_NewPosting
(@PostSubject nvarchar (256), @CustNumber varchar (1000), @UserID int, @CustQuestion ntext)
AS

INSERT INTO dbo.Tbl_Postings
(PostSubject,
CustNumber,
UserID,
CustPostDate)

VALUES(@PostSubject,
@CustNumber,
@UserID
GETDATE())

SET @PostID = @@IDENTITY

EXEC sp_NewQuestion @PostID, @CustQuestion, @UserID

RETURN

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-04 : 14:15:39

Here is the code that I wrote to test it:

----Sample Tables
create table table1
(
Column1 int identity (1, 1) not null,
Column2 int not null
)

create table table2
(ColumnA int not null,
ColumnB int
)

----Stored procedures
create proc usp_test2
(@holdidentity int, @value2 INT)
as

insert into table2 (ColumnA, ColumnB) values (@holdidentity, @value2)

return

create proc usp_Test1
(@value1 INT, @value2 INT)
as

declare @holdidentity int

insert into table1 (Column2) values(@value1)

set @holdidentity = @@IDENTITY

EXEC usp_test2 @holdidentity, @value2

return

----Now run first stored proc that calls second
exec usp_test1 1, 2

----Now check the data
select * from table1
select * from table2

----Now drop the objects
drop proc usp_test1
drop proc usp_test2
drop table table1
drop table table2

Tara

Sirrombor
Starting Member

12 Posts

Posted - 2003-08-04 : 15:12:08

I got it working!

I had PostID declared wrong in the 2nd table....

Thanks Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources