Ad:

MS SQL Discussion Thread
Marked Solved
Views: 1546

Oct 30th, 2009

Stored Procedure with Increments

Expand Post »

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!

sql Syntax (Toggle Plain Text)
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
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

Last edited by peter_budo; Oct 30th, 2009 at 7:30 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

wchitamb

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

31 posts
since Jul 2005

Permalink

Oct 31st, 2009

Re: Stored Procedure with Increments

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]

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Nov 2nd, 2009

Re: Stored Procedure with Increments

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. [at the moment I m just working on the stored procedures not the frontend]

<TABLE A>
Surname
Initials
AAA_NUMBER

<TABLE B>
BBBNUMBER
DOCID
ALLOCATIONDATE

<TABLE C>
CURRENT_YEAR
Sequence
Last_Num

[i will post the table structure again trying to format it]

Last edited by wchitamb; Nov 2nd, 2009 at 6:12 am.

wchitamb

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

31 posts
since Jul 2005

Permalink

Nov 2nd, 2009

Re: Stored Procedure with Increments

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

wchitamb

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

31 posts
since Jul 2005

Permalink

Nov 2nd, 2009

Re: Stored Procedure with Increments

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

MS SQL Syntax (Toggle Plain Text)
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
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?

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Nov 3rd, 2009

Re: Stored Procedure with Increments

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

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

31 posts
since Jul 2005

Permalink

Nov 4th, 2009

Re: Stored Procedure with Increments

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

wchitamb

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

31 posts
since Jul 2005

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Integer or Numeric

Next Thread in MS SQL Forum Timeline: creating headr name dynamically

DaniWeb Message

Cancel Changes

User Name
Password