SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Optional OUTPUT parameter in SP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllearner
Aged Yak Warrior

639 Posts

Posted - 07/22/2006 :  00:06:08  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 07/22/2006 :  02:13:53  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 07/22/2006 :  05:03:22  Show Profile  Reply with Quote
You should worry more on performance due to the cursor than the OUTPUT parameter.


KH

Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 07/22/2006 :  14:13:40  Show Profile  Reply with Quote
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.
Go to Top of Page

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 07/22/2006 :  15:08:12  Show Profile  Reply with Quote
Just post your stored procedures. Maybe someone can find a way to combine them so you don't need a cursor.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/22/2006 :  15:12:44  Show Profile  Reply with Quote
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
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 07/22/2006 :  16:37:00  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/22/2006 :  17:03:08  Show Profile  Reply with Quote
"... 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
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 07/22/2006 :  17:16:59  Show Profile  Reply with Quote
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.....
Go to Top of Page

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 07/22/2006 :  21:24:07  Show Profile  Reply with Quote
Yes you can pass in xml data to stored procedures and use the OPENXML function to convert xml data into a table.
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 07/22/2006 :  22:13:28  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/23/2006 :  02:35:59  Show Profile  Reply with Quote
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
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 07/23/2006 :  04:17:19  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/23/2006 :  05:52:15  Show Profile  Reply with Quote
"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
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 07/24/2006 :  13:48:45  Show Profile  Reply with Quote
IF I join with the XML data or the temp table how can I process emp_number by emp_number
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/24/2006 :  16:51:06  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000