Stored Procedure with Increments

[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

[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]

[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

[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

[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?

[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.

[wchitamb]

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