Sql server optional output parameters

By: 744 Date: 22.07.2017

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

Our new SQL Server Forums are live! We've restricted the ability to create new threads on these forums. Over the next few days we are going to start the migration to a new forum application.

I'm going to start with high post count and active users. You may receive an invitation to the new forums. It's just me trying to seed the user base. My goal is to open it up over the weekend.

sql server optional output parameters

Once we open the site we'll block registrations here. We should be open shortly on the new site. DustinMichaels Constraint Violating Yak Guru.

United Kingdom Posts. Register Now and get your question answered! This Sp will be looping in an cursor for more than 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.

sql server optional output parameters

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

JetBrains DataGrip: Many databases, one tool

You should worry more on performance due to the cursor than the OUTPUT parameter. How can I do it without cursor.

Stored Procedures - Output Parameters & Return Values - SQL Server Wiki - SQL Server - Toad World

I know it going to hit the performance. I don't have any option. DustinMichaels Constraint Violating Yak Guru Posts. Just post your stored procedures. Maybe someone can find a way to combine them so you don't need a cursor. Kristen Test United Kingdom Posts. 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 IF I populate the temp table How can I use a XML to process..

Interesting problem with getting OUTPUT parameters from SQL Server using C# | The oxicivaru.web.fc2.com Forums

That's the joy of a system based on SProcs! Add an additional parameter called "EmployeeList" and make the existing "EmployeeID" parameter optional.

Default Output Parameter Value is Not Returned

If you get a List and no ID then process the list instead of the single ID FROM SourceTable AS S JOIN TempTable AS T on T. EmployeeID " How can I use a XML " XML is just an alternative to passing a LIST or a Temporary Table 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.

sql server optional output parameters

Can I read from XML and pass it through the SP without using cursors Yes you can pass in xml data to stored procedures and use the OPENXML function to convert xml data into a table. 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. 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? This page was generated in 0.

inserted by FC2 system