Member Avatar for wchitamb

I need some help I have managed to get this far (as below), from thatI can put a AAA_NUMBER and be able to get that record details and thats the 1st step that I wanted the 2nd step that I want is say if the DOCID is not available I then need the system to generate a new number (the tricky bit for me now is to have it do the increment since I want the system to 1st look at the current year since each and every year has a different sequence) so the YEAR_SEQUENCE table has CURRENT_YEAR, SEQUENCE, LAST_NUM Fields so it will be incrementing from the last generated number (+1 increment)

Any suggestions or twicks are welcome!

CREATE PROCEDURE Sp_Show_Presc_Num

@AAA_NUMBER VARCHAR(7) 

AS 

SELECT
<TABLE A>.Surname,
<TABLE A>.Initials,
<TABLE A>.AAA_NUMBER,
<TABLE B>.DOCID,
CONVERT(VARCHAR (10),<TABLE B>.ALLOCATIONDATE,103) AS ALLOCATIONDATE,
<TABLE A>.Sex_Code
FROM <TABLE A>
LEFT JOIN <TABLE B> ON <TABLE B>.BBBNUMBER = <TABLE A>.AAA_NUMBER
WHERE AAA_NUMBER=@AAA_NUMBER

EXECUTION EXAMPLE
---------------------------------------------------------------------USE [TEST DB]
EXEC [Sp_Show_Presc_Num] '4300058'
---------------------------------------------------------------------

MORE INFO
Some of the them are populated and the DocID should be displayed if available or else if its NULL then there should be an option for the user to have one generated

The are 3 table
<TABLE A> which will display these fields(Surname, Initials, AAA_NUMBER)
<TABLE B> which will display these fields(DOCID,ALLOCATIONDATE)
<TABLE C> which has these fields (@ the moment)(CURRENT_YEAR, SEQUENCE, LAST_NUM)

the ALLOCATIONDATE is populated when the DOCID is generated so its on that day

Edited by peter_budo because: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)
  • 2 Contributors
  • 6 Replies
  • 234 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by wchitamb

Dani AI

Generated

Brief summary tied to the thread: wants a sproc that returns the patient info (TableA) and DOCID/ALLOCATIONDATE (TableB), and—only if requested—generates a new year-scoped DOCID using the counter in TableC. was right to flag UI vs DB responsibilities; adding a boolean parameter like @CreateIfMissing BIT = 0 to the sproc is a clean, safe choice so the caller controls creation.

Two robust options to generate the increment safely

  • Use a SQL Server SEQUENCE (if the server is 2012+). Create one sequence per-year (or a shared sequence and combine a YEAR prefix) and call NEXT VALUE FOR inside the sproc, then build the DOCID as YEAR + zero-padded sequence. This is atomic and fast, but sequence caches can produce gaps (expected behaviour). (learn.microsoft.com)
-- example (one-time)
CREATE SEQUENCE dbo.DocSeq_2026 AS INT START WITH 1 INCREMENT BY 1 NO CYCLE;

-- usage inside sproc
DECLARE @seq INT = NEXT VALUE FOR dbo.DocSeq_2026;
DECLARE @DocID VARCHAR(16) = CAST(YEAR(GETDATE()) AS CHAR(4))
  + RIGHT('000000' + CAST(@seq AS VARCHAR(6)),6);
-- insert into TableB with ALLOCATIONDATE = GETDATE()
  • Keep the per-year counter in TableC (CURRENT_YEAR / LAST_NUM). Use an atomic update to increment-and-return the value (preferred) or an UPDATE with locking hints inside a short transaction. The “one-statement” update pattern (SET @n = Last_Num = Last_Num + 1) or using OUTPUT avoids the select-then-update race and the cancellation hazard of multi-statement sequences. When a lock hint is needed, apply WITH (UPDLOCK, HOLDLOCK) on the row. ()
SET XACT_ABORT ON;
BEGIN TRAN;
DECLARE @newSeq INT;
UPDATE YearSequence WITH (UPDLOCK, HOLDLOCK)
SET @newSeq = Last_Num = Last_Num + 1
WHERE Current_Year = @year;
-- build DOCID and INSERT into TableB (ALLOCATIONDATE = GETDATE())
COMMIT TRAN;

Practical hardening and notes

  • Enforce a UNIQUE index/constraint on DOCID so duplicate inserts fail fast and can be retried/handled. (learn.microsoft.com)
  • Wrap creation in SET XACT_ABORT ON + TRY/CATCH and explicit ROLLBACK to avoid orphan transactions. (documentation.help)
  • As an alternative to row locks, sp_getapplock can serialize complex generation logic at the DB level (useful for complex rules). (learn.microsoft.com)

These choices cover modern and legacy SQL Server installations, handle concurrency, and keep the UI decision (create vs. just display) at the caller level while keeping generation logic safe in the database.

Recommended Answers

All 6 Replies

Member Avatar for sknake

I don't understand what you are asking. Can you post your table structures with sample on this thread (with code tags) and explain what you are trying to accomplish?

[code]

...code here...

[/code]

Member Avatar for wchitamb

Right thanks, I m sorry if I wasn’t clear. I have 3 tables (structured as below) and all are in SQL Server. what I want to do is have a front-end like .Net or any other but the backend is SQL.

[The purpose is to get the DOCID on <TABLE B> and if its not there already then it has to be generated]
Now I want to have stored procedures that will do this job and what will be happening is a user will input the AAA_Number that will first be validated (seven numbers [1 – 9]) and if it’s a valid number it will be checked on both <TABLE A> and <TABLE B> [NB: AAA_Number = BBBNumber] and if there is a DOCID on <TABLE B> it will be displayed together with some info on <TABLE A>

Now if for some reason if the DOCID is not available then there should be an option to create/ generate one. Now on the new DOCID that will be created it will be based on the current year coz they have different sequences, (which implies they will be changing dependinding on which year they are created) but the general rule will be the +1 increment

So in short it’s a stored procedure that gets input to get a the DOCID + the other info, and if the DOCID is not existent then the other info [Surname,Initials,Sex] will be dispalayed but an option to generate one will be given, and the new DOCID will be based on the current year (the 1st one then everytime a new one is created the +1 increment is applied till the year is over and start again) the date that DOCID is generated will be recoded in the ALLOCATIONDATE field. Below are the tables. Hope I v managed to answer or write clearly wot I intent to do. [[B]at the moment I m just working on the stored procedures not the frontend[/B]]

<TABLE A>
Surname
Initials
AAA_NUMBER

<TABLE B>
BBBNUMBER
DOCID
ALLOCATIONDATE

<TABLE C>
CURRENT_YEAR
Sequence
Last_Num

Edited by wchitamb because: n/a
Member Avatar for wchitamb

In simple terms

If DocID on <TABLE B>
Select Surname, Initials, and Sex from <TABLE A>
Select DocID,AllocationDate from <TABLE B>

If DocID not on <TABLE B>
Create new DocID (insert on <TABLE B>)
Select Surname, Initials, and Sex from <TABLE A> (These will be displayed too)
The new DocID structure is controlled by the current year – since diff years have diff sequences

Member Avatar for sknake

Are you wanting a condition result set or a case to handle this?

IF EXISTS(Select * From TableB Where DocId = 12345)
BEGIN
  PRINT 'Select Surname, Initials, and Sex from <TABLE A>'
  PRINT 'Select DocID,AllocationDate from <TABLE B>'
END ELSE
BEGIN
  PRINT 'Create new DocID (insert on <TABLE B>)'
  PRINT 'Select Surname, Initials, and Sex from <TABLE A> (These will be displayed too)'
  PRINT 'The new DocID structure is controlled by the current year – since diff years have diff sequences '
END

>> Now if for some reason if the DOCID is not available then there should be an option to create/ generate one
I think you're mixing the database layer and user interface layer here. Options should be given at the application level for the most part unless you want to add a parameter to the sproc?

commented: Thanks for putting me on the right track +5
Member Avatar for wchitamb

Thanks. Yeah! I would like to add a parameter to the stored proc, and also for now if possible I would like to be able to do the whole process of generating the DocIds without a frontend.

Member Avatar for wchitamb

Thanks sknake i have now managed to solve it. Once again Thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.