T-SQL - In Single Stored procedure want to pass value(result) of one query to another query as input
Here in my SP, insertion of value and generation of RowID (PrimaryKey) is done succ开发者_StackOverflowessfully.
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, BrightsyYou 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;
精彩评论