Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting Data back from a Stored Proc


Getting Data back from a Stored Proc

Author
Message
mike 57299
mike 57299
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 507
Chad,

Thank you for your help. I will adapt the code to work for me.

Mike
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
This seems like a perfectly reasonable attempt to implement a Sequence Table to me.
The code can be simplified:


-- Sequence table, holds next PK per table
CREATE TABLE dbo.GPK
(
table_name SYSNAME NOT NULL PRIMARY KEY,
next_value INTEGER NOT NULL
);
GO
-- Example row
INSERT dbo.GPK (table_name, next_value) VALUES (N'dbo.MyTable', 0);
GO
-- Procedure to allocate a new PK
CREATE PROCEDURE dbo.usp_GenPK(@table_name SYSNAME, @NewID INT OUTPUT)
AS
BEGIN
UPDATE dbo.GPK
SET @NewID = next_value = next_value + 1
WHERE table_name = @table_name;
END;
GO
-- Test
DECLARE @NewID INTEGER;
EXEC dbo.usp_GenPK N'dbo.MyTable', @NewID OUTPUT;
SELECT @NewID;



edit: forgot the schema prefix



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45074 Visits: 39908
Paul White NZ (7/14/2010)
This seems like a perfectly reasonable attempt to implement a Sequence Table to me.
The code can be simplified:


-- Sequence table, holds next PK per table
CREATE TABLE dbo.GPK
(
table_name SYSNAME NOT NULL PRIMARY KEY,
next_value INTEGER NOT NULL
);
GO
-- Example row
INSERT dbo.GPK (table_name, next_value) VALUES (N'dbo.MyTable', 0);
GO
-- Procedure to allocate a new PK
CREATE PROCEDURE dbo.usp_GenPK(@table_name SYSNAME, @NewID INT OUTPUT)
AS
BEGIN
UPDATE GPK
SET @NewID = next_value = next_value + 1
WHERE table_name = @table_name;
END;
GO
-- Test
DECLARE @NewID INTEGER;
EXEC dbo.usp_GenPK N'dbo.MyTable', @NewID OUTPUT;
SELECT @NewID;




Classic T-SQL therem Paul. That's how I resolved more than 640 deadlocks a day on a sequence table at a previous job.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Jeff Moden (7/14/2010)
Classic T-SQL there Paul. That's how I resolved more than 640 deadlocks a day on a sequence table at a previous job.

Thanks, Jeff. It is easy to get Sequence Tables wrong, as your experience showed.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
mike 57299
mike 57299
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 507
Paul,

Do I need to do an explicit Begin Trans / Commit to make sure there is no duplicate sequence #'s?

Mike
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
spaghettidba
spaghettidba
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5730 Visits: 13305
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
spaghettidba
spaghettidba
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5730 Visits: 13305
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
jcrawf02
jcrawf02
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1420 Visits: 19324
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search