Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Getting Data back from a Stored Proc Expand / Collapse
Author
Message
Posted Wednesday, July 14, 2010 5:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:27 PM
Points: 138, Visits: 369
Chad,

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

Mike
Post #952789
Posted Wednesday, July 14, 2010 10:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 11,194, Visits: 11,167
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #952834
Posted Wednesday, July 14, 2010 10:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 37,099, Visits: 31,650
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #952842
Posted Wednesday, July 14, 2010 11:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 11,194, Visits: 11,167
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #952847
Posted Wednesday, July 14, 2010 11:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:27 PM
Points: 138, Visits: 369
Paul,

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

Mike
Post #952859
Posted Thursday, July 15, 2010 12:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 11,194, Visits: 11,167
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #952863
Posted Thursday, July 15, 2010 2:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:22 AM
Points: 5,045, Visits: 10,570
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #952922
Posted Thursday, July 15, 2010 3:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 11,194, Visits: 11,167
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #952949
Posted Thursday, July 15, 2010 4:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:22 AM
Points: 5,045, Visits: 10,570
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #952976
Posted Thursday, July 15, 2010 5:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:04 AM
Points: 2,678, Visits: 19,271
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."
Post #953023
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse