Getting Data back from a Stored Proc

Question

Getting Data back from a Stored Proc

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

mike 57299 (7/14/2010)
Paul,
Do I need to do an explicit Begin Trans / Commit to make sure there is no duplicate sequence #'s?
Mike

Hey Mike,

No - and that's the beauty of it: it's an atomic operation (single UPDATE statement) which occurs within its own implicit transaction, as normal.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Paul White NZ (7/15/2010)
mike 57299 (7/14/2010)
Paul,
Do I need to do an explicit Begin Trans / Commit to make sure there is no duplicate sequence #'s?
Mike
Hey Mike,
No - and that's the beauty of it: it's an atomic operation (single UPDATE statement) which occurs within its own implicit transaction, as normal.
Paul

It's very similar to the sequence generator I use in my DB, but consider that this solution can lead to very extensive row locks if run inside transactions.

Example:

Open a new query in SSMS and run:

BEGIN TRAN

EXEC dbo.usp_GenPK N'dbo.MyTable', @NewID OUTPUT;

WAITFOR DELAY '00:02';

COMMIT

Open a new query and run the same code. This second query waits for the first transaction to end.

If you don't mind gaps in the sequence, use a CLR procedure that implements autonomous transactions (not enlisted in the context connection's transaction) to generate the new id. Unfortunately, the permission to open a non-context connection in a CLR procedure must be granted marking the assembly as "external" or "unsafe".

I'm writing an article on this subject and I hope I get it finished soon.

-- Gianluca Sartori

Paul White SSC Guru Points: 150467 More actions · Answer 3

Gianluca Sartori (7/15/2010)
...but consider that this solution can lead to very extensive row locks if run inside transactions...

Yes that's one of the things to watch out for, though it's not limited to row locks of course.

There's nothing wrong with using a CLR function to work around this (the EXTERNAL_ACCESS permission set is required, but that's not something to worry about). The other solution, in SQL Server 2008, is to use a loopback linked server, using sp_serveroption to set remote proc transaction promotion off. This allows autonomous transactions too.

I also wrote an article about this last year, but decided it would not be of interest to sufficient people to make it worth publishing.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Paul White NZ (7/15/2010)
The other solution, in SQL Server 2008, is to use a loopback linked server, using sp_serveroption to set remote proc transaction promotion off. This allows autonomous transactions too.

That's the simplest way to achieve it. Unfortunately it works only in SQL 2008 and I had to code in CLR because we're still on SQL 2005.

I also wrote an article about this last year, but decided it would not be of interest to sufficient people to make it worth publishing.

I strongly disagree. If it's well written (and I'm sure it is) it's always worth publishing. It's a subject that I see around sometimes and I think it can solve lots of problems. Autonomous transactions can save your life in many situations (sequences, logging, auditing) and other RDBMS vendors implement it natively (Oracle, DB2, Firebird, PostgreSQL...). It's a shame that MS hasn't decided yet to add this feature to SQL Server.

-- Gianluca Sartori

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 5

I strongly agree with Gianluca, Paul. Just because I don't have a clue what you meant (yet) doesn't mean that I won't need to know that the technique exists, and can refer back to your article when that happens. 😉

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Paul White SSC Guru Points: 150467 More actions · Answer 6

Thanks for the encouragement guys 🙂

I will revisit my decision.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 7

I'll chime in as well, Paul. People every day still use @@identity and forgo transaction log backups. If you've solved it, likely there are a few thousand people out there that don't understand it.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

Paul White NZ (7/15/2010)
Gianluca Sartori (7/15/2010)
...but consider that this solution can lead to very extensive row locks if run inside transactions...
Yes that's one of the things to watch out for, though it's not limited to row locks of course.
There's nothing wrong with using a CLR function to work around this (the EXTERNAL_ACCESS permission set is required, but that's not something to worry about). The other solution, in SQL Server 2008, is to use a loopback linked server, using sp_serveroption to set remote proc transaction promotion off. This allows autonomous transactions too.
I also wrote an article about this last year, but decided it would not be of interest to sufficient people to make it worth publishing.
Paul

Another solution is to have a built-in "Increment" on the NextID proc and write code to know the correct answers ahead of time. No sense in a sequence table requiring RBAR.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)