Optional OUTPUT parameter in SP

[SqlHunter]

CREATE PROCEDURE [dbo].[usp_emp_detail_i]

@emp_number VARCHAR(20),
@ref_table Varchar(100)=NULL OUTPUT

AS


DECLARE @error_int INT


INSERT INTO ref_table
(
emp_number,
name,
age
)

SELECT
emp_id,
emp_name,
emp_age
FROM
tbl_primary_info
WHERE
emp_id=@emp_number

SELECT @error_int=@@error
IF @error_int <> 0
BEGIN
@ref_table='ref_table'
RETURN @error_int
END

Above shown is the stored procedure..Here I need the ref_table to be returned if I encounter with an error
95 % times it will not have any error.In this case will my @ref_table output parameter cause any issues in terms of
performance

In the other 5% I will get the @ref_table as the output parameter.

This Sp will be looping in an cursor for more than 25000 times.Will a declaration for OUTPUT cause any performance issue
This method is used for all the Sp's looping in the cursor to get error causing tables

 

[SQLSister]

Why on earth would you run this proc in a cursor? Why not update all the records at once? That would save you hours of processing time.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[SqlHunter]

have 3 stored procedure
1. Main Sp
2. Module Sp
3. Detail level SP

Eg:
usp_populate_ivoc is the main SP

I have module sp like usp_emp_info which is called inside the main Sp

I have different Sps called inside the Sp usp_emp_info
1. usp_emp_detail_i
2. usp_emp_address_i
3. usp_emp_contact_i
4. usp_emp_detail_u
5. usp_emp_address_u
6. usp_emp_contact_u

The requirement is to process each of the emp_number and if there is any error encountered it should be trapped.

If I use mass update I won't be able to trap the exact error and I won't be able to skip to the next emp_number if there is error.