T-SQL - In Single Stored procedure want to pass value(result) of one query to another query as input.

Question

Hi All,
I have a Stored procedure, in which I have to insert 3 strings into 3 different Tables at a time, each string into each of the 3 tables.
In each table, a unique primary key (rowid) would be generated on insertion of the value.
Now, the Primary Key of first two tables is the Foreign key of the Third Table which as you all know, should not be null.

Here in my SP, insertion of value and generation of RowID (PrimaryKey) is done successfully.
Now I have to pass the two primary keys(Rowids) as values/Parameters(foreignkeys) into the third table, which is returning null.

Here is my SP:-

    (1st Table)    
   INSERT INTO [kk_admin].[FA_Master](FA_Name,FA_CSession,FA_MSession) Values
             (@FA_Name,@Session_Id,@Session_Id)

   SELECT @**FA_ID=FA_ID** FROM [kk_admin].[FA_Master] where FA_Name=@FA_Name  

    (2nd Table)  
   INSERT INTO [kk_admin].[Dept_Master](Dept_Name,Dept_CSession,Dept_MSession) Values  
   (@Dept_Name,@Session_Id,@Session_Id)  

   SELECT @**Dept_id=Dept_id** from [kk_admin].[Dept_Master] where Dept_Name=@Dept_Name   

   (3rd Table)
   INSERT INTO [kk_admin].[Category_Master] (**FA_Id**,**Dept_Id**,Category_Name,Category_CSession,Category_MSession) Values    (@**FA_ID**,@**Dept_Id**,@Category_Name,@Session_Id,@Session_Id)  

Hope everyone understood what I have explained.

Plz Help me,
Iam running out of time. Plz help me. Thank You in Advance,
Brightsy

Answer 1

You can use an OUTPUT clause (assuming you're using SQL Server 2005) to capture the primary key for the two rows you're inserting with the first two queries. You can capture the values into a temporary table. [I previously wrote that you could use a regular variable, but that's not supported.] Example code:

CREATE TABLE #FA_Master_ID ( ID int );
CREATE TABLE #Dept_Master_ID ( ID int );

INSERT kk_admin.FA_Master ( FA_Name, FA_CSession, FA_MSession )
OUTPUT INSERTED.ID INTO #FA_Master_ID
VALUES ( @FA_Name, @Session_Id, @Session_Id );

INSERT kk_admin.Dept_Master ( Dept_Name, Dept_CSession, Dept_MSession )
OUTPUT INSERTED.ID INTO #Dept_Master_ID
VALUES ( @Dept_Name, @Session_Id, @Session_Id );

INSERT kk_admin.Category_Master ( **FA_Id**, **Dept_Id**, Category_Name, Category_CSession, Category_MSession )
SELECT  **FA_Id** = ( SELECT TOP 1 ID FROM #FA_Master_ID ),
        **Dept_Id** = ( SELECT TOP 1 ID FROM #Dept_Master_ID ),
        Category_Name = @Category_Name,
        Category_CSession = @Session_Id,
        Category_MSession = @Session_Id;