want to call SP from another 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

General SQL Server Forums

New to SQL Server Programming

want to call SP from another sp

Author

Topic

ujjwal
Starting Member

4 Posts

Posted - 2006-09-21 : 04:08:43

Hi all,
I have 2 tables like that
CREATE TABLE tab1 (
id int IDENTITY (1, 1) NOT NULL ,
master varchar (50) NULL
) ON PRIMARY
GO

CREATE TABLE tab2 (
id int IDENTITY (1, 1) NOT NULL ,
student_name varchar (50) NULL ,
master_id int NULL
)

Now i want to create a Sp
which is

create proc inner_sp(@s1 varchar(50))
as
insert into tab1(master) values (@s1)

and i want to call it from another sp

create proc outer_sp2(@s2 varchar(50),@s1 varchar(50))
as
declare @r int
exec inner_sp @s1
set @r=Scope_Identity()
insert into tab2(student_name,master_id) values(@s2,@r)

exec outer_sp2 'Vivek1','Test'

But in table tab2 master_id column contain NULL value....
Please fix it...
Ujjwal

ujjwal
Starting Member

4 Posts

Posted - 2006-09-21 : 04:11:04

problem is that in table tab2 contain master_id NULL .It should contain the last inserted id(integer value) of Table tab1.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-09-21 : 04:15:54

Welcome to SQLTeam,

i would do it like thus;


create proc inner_sp(@s1 varchar(50))
as
insert into tab1(master) values (@s1)

set @r=Scope_Identity()
insert into tab2(student_name,master_id) values(@s2,@r)

exec outer_sp2 'Vivek1','Test'

you dont need the second SP

Afrika

ujjwal
Starting Member

4 Posts

Posted - 2006-09-21 : 04:17:58

No sir I did That but master_id column of tab2 table insert 'NULL' value.

ujjwal
Starting Member

4 Posts

Posted - 2006-09-21 : 04:28:47

Sorry africa,
My primary job 2 call store procedure from another store procedure.
and in this case my inserted value in tab2 table is 'NULL' in the master_id column .
Which should insert the auto increment id valueof tab1 table.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-21 : 07:35:53

get the last inserted identity value with scope_identity() in the first sproc
and pass that as a paramter to the second sproc

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources