October 3, 2011 at 1:51 pm
Hi i have two tables
1. Table A
ID---Name
1----Abs
2----Cex
3---Dex
Here ID in table A is an Identity Key
Table B
BID---AID--Bnum
1------1-----123
2------1-----234
3------3------146
Here initially i wrote a stored procedure which will be inserting datainto TableA
Create Procedure SPA
@Name varchar(10)
As
declare @ID int
Insert Into A
Values(Sat)
set @ID=scope_identity()
Now i got to write a stored procedure SPB which will insert values into table B based on the Inserted values in Table A
like i should get the inserted ID in table A and insert that into table B
Here i'm getting stuck. How to make that inserted ID in table A as output of the storedProcedure SPA and make it as an input to the StoredProcedureSPB
I tried it like
Create ProcedureSPB
@Name varchar(10),
@Bnum varchar(5),
As
Declare AID int,
ID int
Exec SPA @name
setAID=@ID
insert into B
Values(@AID,@BNUM)
Exec SPB 'Sat','567'
Here when i exec SPB stored procedure my first SPA is getting executed by i'm not getting that inserted ID value returned into AID.
Can some one help me out with this please
October 3, 2011 at 2:33 pm
I think what you are trying to do is call SPB and inside that proc you want to call SPA?
Create Procedure SPA
@Name varchar(10),
@ID int output
As
Insert Into A
Values(Sat)
set @ID=scope_identity()
Create ProcedureSPB
@Name varchar(10),
@Bnum varchar(5),
As
declare @ID int
Exec SPA @name, @ID output
Insert into B
Values(@ID, @BNUM)
something like that. Your explanation is not very clear and you seem to have an extra variable or two floating around. see if you can make something like my explanation work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply