Optional OUTPUT parameter in SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Optional OUTPUT parameter in SP

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-22 : 00:06:08

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

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-22 : 02:13:53

quote:

This Sp will be looping in an cursor for more than 25000 times.

Try avoiding the Cursor, this may Hit the Performance enormously.
I dont think, setting an output parameter will Hit any performance.

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-22 : 05:03:22

You should worry more on performance due to the cursor than the OUTPUT parameter.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-22 : 14:13:40

There is one Main Sp which calls multiple Sps and the main Sp will be passed with emp_number.Since this needs to be called for each emp_number.How can I do it without cursor.I know it going to hit the performance.I don't have any option.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-07-22 : 15:08:12

Just post your stored procedures. Maybe someone can find a way to combine them so you don't need a cursor.

Kristen
Test

22859 Posts

Posted - 2006-07-22 : 15:12:44

Couldn't you pass a LIST of Employee Numbers, instead of a single one, to your Sproc ??

Or a temporary table full of the Employee IDs to be processed ...

... or an XML list ...

... or some other such device!

Kristen

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-22 : 16:37:00

I can't pass a list of emp_numbers because all the sp's are written to process one emp_number at a time.So there are around 50 sp's which are insert and update sp's for each emp_number....

IF I populate the temp table....Still I have to process each emp_number through the cursor...

How can I use a XML to process..still I will have to get each emp_number and process it right

Kristen
Test

22859 Posts

Posted - 2006-07-22 : 17:03:08

"... all the sp's are written to process one emp_number at a time"

So change them! That's the joy of a system based on SProcs!

Add an additional parameter called "EmployeeList" and make the existing "EmployeeID" parameter optional. If you get a List and no ID then process the list instead of the single ID ...

"IF I populate the temp table....Still I have to process each emp_number through the cursor..."

I reckon you could just JOIN it:


INSERT INTO TargetTable ( ... column list ...)
SELECT ... columnlist ...
FROM SourceTable AS S
     JOIN #TempTable AS T
         on T.EmployeeID = S.EmployeeID

"How can I use a XML"
XML is just an alternative to passing a LIST or a Temporary Table ...

Kristen

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-22 : 17:16:59

IF I process based on a no ID the problem to trap the errors would be difficult and if one fails in the group the entire group fails.Plus the no of records to process are really huge.IF its a mass insert/update and the users are using the application there is high possibilty of table getting locked....

Can I read from XML and pass it through the SP without using cursors.....

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-07-22 : 21:24:07

Yes you can pass in xml data to stored procedures and use the OPENXML function to convert xml data into a table.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-22 : 22:13:28

Can I execute my main Sp with all the emp_numbers one at a time so that I can capture the error for the emp if happens
and move on to the next transaction

Kristen
Test

22859 Posts

Posted - 2006-07-23 : 02:35:59

What errors are you currently capturing?

If a duplicate already exists, or you get a DEADLOCK ERROR, your current SProc is just going to fall flat on its face (and not return anything useful), isn't it?

If you needed to "catch" duplicate entries (which is about the only thing you could catch I reckon) then you would have to add code to establish if there were any BEFORE the insert, create a suitable error-return-list, and then insert the good ones using WHERE NOT EXISTS.

(In fact I would put all the Employee IDs in a Temp Table with another column to indicate any "validation error" code [e.g. "Duplicate"], flag the rows which have a Validation Error, and then insert the ones which are "clean"

"Plus the no of records to process are really huge"

Then passing them as a list/TempTable is going to be MUCH faster than using a cursor!

"Can I read from XML and pass it through the SP without using cursors....."

As DustinMichaels said, but beware of processing more than a few 100K of XML via that route - we used it for 4 or 5 MB (under SQL 2000) and it was dog slow ...

Kristen

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-23 : 04:17:19

Then passing them as a list/TempTable is going to be MUCH faster than using a cursor!

How will I pass the list of emp_numbers to the main Sp and execute it to the number of emp_numbers

Kristen
Test

22859 Posts

Posted - 2006-07-23 : 05:52:15

"How will I pass the list of emp_numbers to the main Sp"

As a delimited list - then split it into a temporary table and JOIN that to the SELECT collecting the data for the INSERT

(For SPLIT see: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists,Best%20split%20functions)

Or as a temporary table, or as XML ... both of which can also be JOINed as above.

Kristen

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-24 : 13:48:45

IF I join with the XML data or the temp table how can I process emp_number by emp_number

Kristen
Test

22859 Posts

Posted - 2006-07-24 : 16:51:06

I'm missing something 'coz I reckon I've covered that, so there must be a gap in between what I've said and what you have understood - can you ask a more specific question?


INSERT INTO TargetTable ( ... column list ...)
SELECT ... columnlist ...
FROM SourceTable AS S
     JOIN #TempTable AS T
         on T.EmployeeID = S.EmployeeID

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources