Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Edit #1: I updated trigger (<code>IF UPDATE</code>), stored procedure and last two examples.</p> <pre><code>CREATE TABLE dbo.CustomSequence ( CustomSequenceID INT IDENTITY(1,1) PRIMARY KEY, SequenceName NVARCHAR(128) NOT NULL, -- or SYSNAME UNIQUE(SequenceName), RangeStart INT NOT NULL, RangeEnd INT NOT NULL, CHECK(RangeStart &lt; RangeEnd), CurrentValue INT NULL, CHECK(RangeStart &lt;= CurrentValue AND CurrentValue &lt;= RangeEnd) ); GO CREATE TRIGGER trgIU_CustomSequence_VerifyRange ON dbo.CustomSequence AFTER INSERT, UPDATE AS BEGIN IF (UPDATE(RangeStart) OR UPDATE(RangeEnd)) AND EXISTS ( SELECT * FROM inserted i WHERE EXISTS ( SELECT * FROM dbo.CustomSequence cs WHERE cs.CustomSequenceID &lt;&gt; i.CustomSequenceID AND i.RangeStart &lt;= cs.RangeEnd AND i.RangeEnd &gt;= cs.RangeStart ) ) BEGIN ROLLBACK TRANSACTION; RAISERROR(N'Range overlapping error', 16, 1); END END; GO --TRUNCATE TABLE dbo.CustomSequence INSERT dbo.CustomSequence (SequenceName, RangeStart, RangeEnd) SELECT N'Red Card', 1, 50000 UNION ALL SELECT N'Blue Card', 50001, 100000 UNION ALL SELECT N'Green Card', 100001, 150000; GO -- Test for overlapping range INSERT dbo.CustomSequence (SequenceName, RangeStart, RangeEnd) VALUES (N'Yellow Card', -100, +100); GO /* Msg 50000, Level 16, State 1, Procedure trgIU_CustomSequence_VerifyRange, Line 20 Range overlapping error Msg 3609, Level 16, State 1, Line 1 The transaction ended in the trigger. The batch has been aborted. */ GO -- This procedure tries to reserve CREATE PROCEDURE dbo.SequenceReservation ( @CustomSequenceID INT, -- You could use also @SequenceName @IDsCount INT, -- How many IDs do we/you need ? (Needs to be greather than 0) @LastID INT OUTPUT ) AS BEGIN DECLARE @StartTranCount INT, @SavePoint VARCHAR(32); SET @StartTranCount = @@TRANCOUNT; IF @StartTranCount = 0 -- There is an active transaction ? BEGIN BEGIN TRANSACTION -- If not then it starts a "new" transaction END ELSE -- If yes then "save" a save point -- see http://technet.microsoft.com/en-us/library/ms188378.aspx BEGIN DECLARE @ProcID INT, @NestLevel INT; SET @ProcID = @@PROCID; SET @NestLevel = @@NESTLEVEL; SET @SavePoint = CONVERT(VARCHAR(11), @ProcID) + ',' + CONVERT(VARCHAR(11), @NestLevel); SAVE TRANSACTION @SavePoint; END BEGIN TRY UPDATE dbo.CustomSequence SET @LastID = CurrentValue = ISNULL(CurrentValue, 0) + @IDsCount WHERE CustomSequenceID = @CustomSequenceID; IF @@ROWCOUNT = 0 RAISERROR(N'Invalid sequence', 16, 1); COMMIT TRANSACTION; END TRY BEGIN CATCH IF @StartTranCount = 0 BEGIN ROLLBACK TRANSACTION; END ELSE -- @StartTranCount &gt; 0 BEGIN ROLLBACK TRANSACTION @SavePoint END DECLARE @ErrorMessage NVARCHAR(2048), @ErrorSeverity INT, @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH; END; GO SELECT * FROM dbo.CustomSequence; GO -- Example usage #1 DECLARE @LastID INT; EXEC dbo.SequenceReservation @CustomSequenceID = 1, -- Red Card @IDsCount = 2, -- How many IDs ? @LastID = @LastID OUTPUT; SELECT @LastID - 2 + 1 AS [FirstID], @LastID AS [LastID]; GO -- Example usage #2 DECLARE @LastID INT; EXEC dbo.SequenceReservation @CustomSequenceID = 1, -- Red Card @IDsCount = 7, -- How many IDs ? @LastID = @LastID OUTPUT; SELECT @LastID - 7 + 1 AS [FirstID], @LastID AS [LastID]; SELECT * FROM dbo.CustomSequence; GO </code></pre> <p>Results:</p> <pre><code>CustomSequenceID SequenceName RangeStart RangeEnd CurrentValue ---------------- ------------ ----------- ----------- ------------ 1 Red Card 1 50000 9 2 Blue Card 50001 100000 NULL 3 Green Card 100001 150000 NULL </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POCreate Sequence in MS SQL Server 2008
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBogdan Sahlean
    1. USBogdan Sahlean
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCreate Sequence in MS SQL Server 2008
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTDownMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COwhy output parameter called @FromID? it returns current value of sequence - http://sqlfiddle.com/#!3/56f4d/2
      singulars
      1. PO
      1. USRoman Pekar
    2. COwhy do you need all these transactions, you have single statement which would be in transaction anyway (if implicit_tranactions are on). A bit of explanation about how OP should get actual IDs after calling procedure would be good also. At the moment code looks really overcomplicated for me, and it doesn't show actual insert of the card into Cards table
      singulars
      1. PO
      1. USRoman Pekar
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload