SQL Server Performance Forum – Threads Archive

Executing procedure inside a proc…

I am executing a procedure inside a DTS package, where in the SP i call another SP which insert rows one by one passed as a parameter. The inner SP is in a while loop. THe whole setup looks like… — Outer SP
WHILE @Current_Row < = @Row_Count
BEGIN
/* Get the required values to be passed as parameters*/
SELECT @Store_Ref_No = Store_Ref_No , @Cash_Total = Cash_Total, @Voucher_Total = Voucher_Total from @tbl_Bureaux_Trans_Load where Row_Index = @Current_Row /* Call the stored procedure to create RE transactions for the store and update relevant tables*/
EXEC @Return_Status = Usp_Scms_Update_Bureau_Trans @Store_Ref_No, @Cash_Total, @Voucher_Total — Inner SP Now when this inner SP fails, i am handling it by getting the output result and storing the error value in a separate table just becoz i dont what the outer SP to fail but to run for all the values in the while loop without failure.
The DTS Package successfully executes all the values in the while loop successfully regardless of the failure of the inner SP. But after finishing the loop the DTS package fails saying that insert failed. how can i avoid this error message and make the DTS package refrain from throwing that error message.
Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

Hi,
what error it throws ! Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami
————————
http://hemantgirisgoswami.blogspot.com

The error is a normal error we get while trying to insert null into not null columns.. Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column ‘TRANSACTION_AMOUNT’, table ‘tbl_Receipt_Expense_Transaction’; column does not allow nulls. INSERT fails.
The statement has been terminated.
Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

Looks like perhaps someone has heard about cursors but doesn’t know about JOINs. I don’t know the details, but I wouldn’t be surprised.<br /><br />Anyway, why doesn’t the inner sproc check for the NULL and act accordingly? Either skip the insert, or substitute NULL value through ISNULL(<expression>,<value_to_replace_null&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> function.

The problem in doing that is "there is a table variable declared in the outer SP and this table variable gets a row when this inner SP fails" and this table variable is used in some transformations later in the outer SP Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

What does the outer SP do exactly – I wouldn’t be surprised if it does a lookup in another table, based on the parameters. That could be the same doing a JOIN between the columns from which you are now passing on the data as parameters to the outer SP, and the columns on the lookup table from the outer SP.

SPName: Usp_Scms_Bureau_Link_Update
Description: To create RE transactions for Bureau de Change facility
1) Delete unwanted rows from the staging table
2) Call the stored procedure Usp_Scms_Update_Bureau_Trans for each record
3) call the stored procedure Usp_Scms_Bureau_Raise_Alert to raise alerts for data mismatch
4) call the stored procedure Usp_Scms_Bureau_Raise_Alert to raise alerts for update failures step 2 fails when a null value is passed which normal, but the SP handles it by taking the parameter it needs eventhough it fails and SP finishes steps 3 and 4, but the DTS package shows me that it has failed in executing the outer SP I have pasted the exact code below…
CREATE PROCEDURE [dbo].[Usp_Scms_Bureau_Link_Update]
AS
BEGIN
SET NOCOUNT ON
/*Declaration of Local Variables */
DECLARE @Store_Ref_No Char(5)
DECLARE @Cash_Total Money
DECLARE @Voucher_Total Money
DECLARE @Row_Count int
DECLARE @Current_Row int
DECLARE @Alert_ID int
DECLARE @DB_Name Varchar(10)
DECLARE @SP_Name Varchar(50)
DECLARE @Update_Failed_Count int
DECLARE @Return_Status int
/* Delete rows from staging table if both store id and branch name are empty */
DELETE FROM tbl_Bureaux_Trans_Load
WHERE ISNULL(Store_Id,’null’) = ‘null’
AND ISNULL(Branch_Name,’null’) = ‘null’
/*Get Store_ref_no values from the store details table */
UPDATE tbl_Bureaux_Trans_Load
SET tbl_Bureaux_Trans_Load.Store_Ref_No = Store.Store_Ref_No
FROM tbl_Bureaux_Trans_Load Bureau INNER join tbl_Store_Details Store
ON Bureau.Store_Id = Store.Store_Id
/*Declare a table variable */
DECLARE @tbl_Bureaux_Trans_Load TABLE (Row_Index Int Identity,
Store_ID Char(5),
Store_Ref_No Char(5),
Branch_Name Varchar(100),
Opening_Balance Money,
Cash_Total Money,
StgPaid_To_Internal Money,
Closing_Balance Money,
Cheques_Received Money,
Voucher_Total Money,
PL Money,
Commission Money,
GBP_Shorts Money,
GBP_Overs Money,
CreditCard_Shorts Money,
CreditCard_Overs Money,
ChargedCard_Shorts Money,
ChargedCard_Overs Money,
Deposits_Taken Money,
Deposits_Used Money,
Balance_Due_ToFrom_CFE Money,
Net_Due_Check Money,
BCH_Code Varchar,
Update_Failed Char(1)
) /*Get the contents of staging table to the table variable */
INSERT INTO @tbl_Bureaux_Trans_Load
SELECT Store_ID,
Store_Ref_No,
Branch_Name,
Opening_Balance,
Cash_Total,
StgPaid_To_Internal,
Closing_Balance,
Cheques_Received,
Voucher_Total,
PL,
Commission,
GBP_Shorts,
GBP_Overs,
CreditCard_Shorts,
CreditCard_Overs,
ChargedCard_Shorts,
ChargedCard_Overs,
Deposits_Taken,
Deposits_Used,
Balance_Due_ToFrom_CFE,
Net_Due_Check,
BCH_Code,
null
FROM tbl_Bureaux_Trans_Load
WHERE IsNull(Store_Ref_No,”)<>”
/* Get the maximum row count,minimum row count of the table variable */
SELECT @Row_Count = MAX(Row_Index)
,@Current_Row = MIN(Row_Index)
FROM @tbl_Bureaux_Trans_Load
/* Loop through the records of table variable */
WHILE @Current_Row < = @Row_Count
BEGIN
/* Get the required values to be passed as parameters*/
SELECT @Store_Ref_No = Store_Ref_No , @Cash_Total = Cash_Total, @Voucher_Total = Voucher_Total from @tbl_Bureaux_Trans_Load where Row_Index = @Current_Row
/* Call the stored procedure to create RE transactions for the store and update relevant tables*/
EXEC @Return_Status = Usp_Scms_Update_Bureau_Trans @Store_Ref_No,@Cash_Total,@Voucher_Total
/*If the transaction failed for the store */
IF @Return_Status <> 0
BEGIN
/* Set the status of update as ‘failed’ in the table variable */
UPDATE @tbl_Bureaux_Trans_Load SET Update_Failed = ‘Y’ WHERE Row_Index = @Current_Row
END
/* Move the index to the next record */
SET @Current_Row = @Current_Row + 1
END
/* Raise alerts on data mismatch */
EXEC Usp_Scms_Bureau_Raise_Alert
/* Rasie alerts on Update Failure */
SELECT @Update_Failed_Count = Count(1) FROM @tbl_Bureaux_Trans_Load WHERE Update_Failed = ‘Y’
IF @Update_Failed_Count >0
BEGIN /* Pass the no of ‘update failed – stores’ to the stored procedure that raises alert */
EXEC Usp_CC_Create_Alert @Alert_ID OUTPUT,’BR01′,@Update_Failed_Count,0,’Bureau-update fail’
/* Insert the alert details into the table tbl_cc_alert_data */
INSERT INTO tbl_CC_Alert_Data
(ALERT_ID,FIELD_NAME,VALUE_AT_SOURCE1,VALUE_AT_SOURCE2)
SELECT @Alert_ID,(Store_Id+’$$’+Branch_Name),Cash_Total,Voucher_Total
FROM @tbl_Bureaux_Trans_Load
WHERE Update_Failed = ‘Y’
END
END
—————- Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

]]>