identity in stored procedures

I have a stored procedure called sp_Insert_System_Header which inserts a record with an identity. I save this as id.
Then in the sp_Customer_Complaint_Entry stored procedure I want to use this field as a parameter for the case_id field.
When I run I get no errors But also no records get inserted into either table
Any Ideas.
CREATE PROCEDURE sp_Insert_System_Header
(
@.System_Type_IDint,
@.Priority_IDint,
@.PCAR_Manager_IDint,
@.Opportunity_IDint,
@.Status_IDint,
@.Initiated_By_IDint,
@.Location_IDint,
@.Other_Locationvarchar(100),
@.Initiated_OnDateTime,
@.Assigned_By_IDint,
@.Assigned_To_IDint,
@.Assigned_OnDateTime,
@.Resolved_OnDateTime,
@.Closed_BY_IDint,
@.Closed_OnDateTime,
@.Descriptionvarchar(2000),
@.Immediate_Actionsvarchar(2000),
@.Cause_Type_IDint,
@.Actual_Cause_Descriptionvarchar(2000),
@.Corrective_Descriptionvarchar(2000),
@.ID int OUTPUT
)
AS
INSERT INTO HEADER(
System_Type_ID,
Priority_ID,
PCAR_Manager_ID,
Opportunity_ID,
Status_ID,
Initiated_By_ID,
Location_ID,
OtherLocation,
Initiated_On,
Assigned_By_ID,
Assigned_To_ID,
Assigned_On,
Resolved_On,
Closed_BY_ID,
Closed_On,
Description,
Immediate_Actions,
Cause_Type_ID,
Actual_Cause_Description,
Corrective_Description
)
VALUES(
@.System_Type_ID,
@.Priority_ID,
@.PCAR_Manager_ID,
@.Opportunity_ID,
@.Status_ID,
@.Initiated_By_ID,
@.Location_ID,
@.Other_Location,
@.Initiated_On,
@.Assigned_By_ID,
@.Assigned_To_ID,
@.Assigned_On,
@.Resolved_On,
@.Closed_BY_ID,
@.Closed_On,
@.Description,
@.Immediate_Actions,
@.Cause_Type_ID,
@.Actual_Cause_Description,
@.Corrective_Description
)
Select @.ID = @.@.Identity
GO
CREATE PROCEDURE sp_Customer_Complaint_Entry
(
@.System_Type_IDint,
@.Priority_IDint,
@.Opportunity_IDint,
@.Status_IDint,
@.Initiated_By_IDint,
@.Location_IDint,
@.Other_Locationvarchar(100),
@.Initiated_OnDateTime,
@.Assigned_By_IDint,
@.Assigned_OnDateTime,
@.Resolved_OnDateTime,
@.Closed_BY_IDint,
@.Closed_OnDateTime,
@.Descriptionvarchar(2000),
@.Immediate_Actionsvarchar(2000),
@.Cause_Type_IDint,
@.Actual_Cause_Descriptionvarchar(2000),
@.Corrective_Descriptionvarchar(2000),
--THESE PARAMETERS ARE FOR CUSTOMER COMPLAINT
--SYSTEM ONLY
@.BusinessUnit_IDint,
@.RMANumbervarchar(50),
@.Product_Codevarchar(50),
@.Product_Namevarchar(100),
@.Customer_Numbervarchar(50),
@.Customer_Namevarchar(50),
@.Lot_Numbervarchar(50),
@.PO_Numbervarchar(50),
@.ID int OUTPUT
)
AS
Declare @.@.CASE_ID int,
@.@.PCAR_Manager_ID int
--FIND OUT WHO IS THE PCAR MANAGER FOR CUSTOMER COMPLAINT SYSTEM
EXEC
@.@.PCAR_Manager_ID = sp_getPCARID @.System_Type_ID, @.Initiated_By_ID
--ADD NEW RECORD TO SYSTEM HEADER
EXEC
@.@.CASE_ID = sp_Insert_System_Header
@.System_Type_ID,
@.Priority_ID,
@.@.PCAR_Manager_ID,
@.Opportunity_ID,
@.Status_ID,
@.Initiated_By_ID,
@.Location_ID,
@.Other_Location,
@.Initiated_On,
@.Assigned_By_ID,
@.@.PCAR_Manager_ID,
@.Assigned_On,
@.Resolved_On,
@.Closed_BY_ID,
@.Closed_On,
@.Description,
@.Immediate_Actions,
@.Cause_Type_ID,
@.Actual_Cause_Description,
@.Corrective_Description
--ADD NEW RECORD TO CUSTOMER COMPLAINT TABLE
INSERT INTO Customer_Complaint_System(
Case_ID,
BusinessUnit_ID,
RMANumber,
Product_Code,
Product_Name,
Customer_Number,
Customer_Name,
Lot_Number,
PO_Number)
VALUES(
@.@.CASE_ID,
@.BusinessUnit_ID,
@.RMANumber,
@.Product_Code,
@.Product_Name,
@.Customer_Number,
@.Customer_Name,
@.Lot_Number,
@.PO_Number
)
SELECT @.ID = @.@.CASE_ID
GO
I think the problem is incorrect usage of OUTPUT parameters.
The following is how you're doing it, which is actually appropriate syntax
for a RETURN value:
DECLARE @.myVariable INT
EXEC @.myVariable = my_Stored_Proc @.params, ...
For OUTPUT parameters, on the other hand, you do it this way:
DECLARE @.myVariable INT
EXEC my_Stored_Proc @.params, ..., @.myVariable OUTPUT
Does that make sense?
RETURN values, by the way, can only be of the INT datatype, and of course
you can only have one of them. OUTPUT params, on the other hand, can be of
any scaler datatype and you can have as many as you want. So they're quite
useful...
Also, two other comments: A) It's recommended that you not use sp_ to
prefix stored procedures as this is the prefix used for system stored
procedures and will cause a small performance penalty due to the server
looking for your stored procedure in the master database before looking
locally. B) You should probably not use @.@. to prefix variables, as that's
the prefix for system variables. Just a code readability issue.
"jat14" <anonymous@.discussions.microsoft.com> wrote in message
news:D140EA0D-E488-429E-9575-9942D9974D7E@.microsoft.com...
> I have a stored procedure called sp_Insert_System_Header which inserts a
record with an identity. I save this as id.
> Then in the sp_Customer_Complaint_Entry stored procedure I want to use
this field as a parameter for the case_id field.
> When I run I get no errors But also no records get inserted into either
table
> Any Ideas.
>
> CREATE PROCEDURE sp_Insert_System_Header
> (
> @.System_Type_ID int,
> @.Priority_ID int,
> @.PCAR_Manager_ID int,
> @.Opportunity_ID int,
> @.Status_ID int,
> @.Initiated_By_ID int,
> @.Location_ID int,
> @.Other_Location varchar(100),
> @.Initiated_On DateTime,
> @.Assigned_By_ID int,
> @.Assigned_To_ID int,
> @.Assigned_On DateTime,
> @.Resolved_On DateTime,
> @.Closed_BY_ID int,
> @.Closed_On DateTime,
> @.Description varchar(2000),
> @.Immediate_Actions varchar(2000),
> @.Cause_Type_ID int,
> @.Actual_Cause_Description varchar(2000),
> @.Corrective_Description varchar(2000),
> @.ID int OUTPUT
> )
> AS
> INSERT INTO HEADER(
> System_Type_ID,
> Priority_ID,
> PCAR_Manager_ID,
> Opportunity_ID,
> Status_ID,
> Initiated_By_ID,
> Location_ID,
> OtherLocation,
> Initiated_On,
> Assigned_By_ID,
> Assigned_To_ID,
> Assigned_On,
> Resolved_On,
> Closed_BY_ID,
> Closed_On,
> Description,
> Immediate_Actions,
> Cause_Type_ID,
> Actual_Cause_Description,
> Corrective_Description
> )
> VALUES(
> @.System_Type_ID,
> @.Priority_ID,
> @.PCAR_Manager_ID,
> @.Opportunity_ID,
> @.Status_ID,
> @.Initiated_By_ID,
> @.Location_ID,
> @.Other_Location,
> @.Initiated_On,
> @.Assigned_By_ID,
> @.Assigned_To_ID,
> @.Assigned_On,
> @.Resolved_On,
> @.Closed_BY_ID,
> @.Closed_On,
> @.Description,
> @.Immediate_Actions,
> @.Cause_Type_ID,
> @.Actual_Cause_Description,
> @.Corrective_Description
> )
> Select @.ID = @.@.Identity
> GO
>
> CREATE PROCEDURE sp_Customer_Complaint_Entry
> (
> @.System_Type_ID int,
> @.Priority_ID int,
> @.Opportunity_ID int,
> @.Status_ID int,
> @.Initiated_By_ID int,
> @.Location_ID int,
> @.Other_Location varchar(100),
> @.Initiated_On DateTime,
> @.Assigned_By_ID int,
> @.Assigned_On DateTime,
> @.Resolved_On DateTime,
> @.Closed_BY_ID int,
> @.Closed_On DateTime,
> @.Description varchar(2000),
> @.Immediate_Actions varchar(2000),
> @.Cause_Type_ID int,
> @.Actual_Cause_Description varchar(2000),
> @.Corrective_Description varchar(2000),
> --THESE PARAMETERS ARE FOR CUSTOMER COMPLAINT
> --SYSTEM ONLY
> @.BusinessUnit_ID int,
> @.RMANumber varchar(50),
> @.Product_Code varchar(50),
> @.Product_Name varchar(100),
> @.Customer_Number varchar(50),
> @.Customer_Name varchar(50),
> @.Lot_Number varchar(50),
> @.PO_Number varchar(50),
> @.ID int OUTPUT
> )
> AS
> Declare @.@.CASE_ID int,
> @.@.PCAR_Manager_ID int
> -- FIND OUT WHO IS THE PCAR MANAGER FOR CUSTOMER COMPLAINT SYSTEM
> EXEC
> @.@.PCAR_Manager_ID = sp_getPCARID @.System_Type_ID, @.Initiated_By_ID
> -- ADD NEW RECORD TO SYSTEM HEADER
> EXEC
> @.@.CASE_ID = sp_Insert_System_Header
> @.System_Type_ID,
> @.Priority_ID,
> @.@.PCAR_Manager_ID,
> @.Opportunity_ID,
> @.Status_ID,
> @.Initiated_By_ID,
> @.Location_ID,
> @.Other_Location ,
> @.Initiated_On,
> @.Assigned_By_ID,
> @.@.PCAR_Manager_ID,
> @.Assigned_On,
> @.Resolved_On,
> @.Closed_BY_ID,
> @.Closed_On,
> @.Description,
> @.Immediate_Actions,
> @.Cause_Type_ID,
> @.Actual_Cause_Description,
> @.Corrective_Description
> -- ADD NEW RECORD TO CUSTOMER COMPLAINT TABLE
> INSERT INTO Customer_Complaint_System(
> Case_ID,
> BusinessUnit_ID,
> RMANumber,
> Product_Code,
> Product_Name,
> Customer_Number,
> Customer_Name,
> Lot_Number,
> PO_Number)
> VALUES(
> @.@.CASE_ID,
> @.BusinessUnit_ID,
> @.RMANumber,
> @.Product_Code,
> @.Product_Name,
> @.Customer_Number,
> @.Customer_Name,
> @.Lot_Number,
> @.PO_Number
> )
> SELECT @.ID = @.@.CASE_ID
>
> GO
>