regarding date update in stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi All,

I have a stored procedure like below.

SQL

USE [Mama]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

GO
ALTER PROCEDURE [dbo].[IP_BILLNO](@BILLNO NUMERIC(10) OUTPUT)
AS
  DECLARE @Y NUMERIC(4,0),
    @D VARCHAR(12),
    @OUT NUMERIC(12,0)
BEGIN
  SELECT @Y=YEAR(GETDATE())
  SELECT @D='5' + CONVERT(VARCHAR,YEAR(GETDATE())) + '00000'
  SELECT @OUT=MAX(BillNo) from IPTRANS --where BillNo > CONVERT(NUMERIC,@D)
  SELECT @OUT = ISNULL(@OUT,0)
  IF @OUT=0
    SELECT @OUT=CONVERT(NUMERIC,@D)+1
  ELSE
    SELECT @OUT=@OUT+1
  
  SELECT @BILLNO=@OUT
END

The problem is the the year is not updating.
now we are in 2014 year but its generating the bill no with the year 2013 only.
Ex bill no is: 520140123---I want like this but i m getting like 520130123 only.

How can i resolve this problem.
can anyone give suggestions to overcome this problem.

Posted 6-Jan-14 0:06am

Member 10366907

Updated 6-Jan-14 0:09am

Kornfeld Eliyahu Peter

v2

Add a Solution

Comments

Kornfeld Eliyahu Peter 6-Jan-14 6:11am

Can you run this alone and give me the result?
select '5' + CONVERT(VARCHAR,YEAR(GETDATE())) + '00000'

Member 10366907 6-Jan-14 7:21am

the result was 5201400000

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Answer 1 · 2014-01-06T01:32:00

Solution 2

@D IS getting the right value of 2014 but you never use it because of this

SQL

SELECT @OUT=MAX(BillNo) from IPTRANS --where BillNo > CONVERT(NUMERIC,@D)
SELECT @OUT = ISNULL(@OUT,0)

IF @OUT=0

In case you have even one record in IPTRANS you never will use @D to compute a new bill no...

Posted 6-Jan-14 1:32am

Kornfeld Eliyahu Peter

Sibasisjena · Answer 2 · 2014-01-06T00:38:00

Solution 1

SQL

SELECT @OUT=MAX(BillNo) from IPTRANS

The above statement is selecting 520130122 to @OUT variable
Then the Else condition is executed then it is giving an output : 520130122+1

First think what you want, accordingly write the code.

Posted 6-Jan-14 0:38am

Sibasisjena

Updated 6-Jan-14 0:40am

v2

Christian Graus · Answer 3 · 2014-01-06T11:32:00

SELECT @OUT=MAX(BillNo) from IPTRANS

This is the only actual DB operation being done here. As such, it's the only thing the proc should do, the rest should be done in your procedural code. Otherwise, a business rule is hidden in the DB, and is harder to debug as a result. The only exception I can think of, is if you want to deploy code, and be able to change this process without changing the code base. Even then, it seems a bit crazy to me. I can't work out what you're trying to do, or how it makes sense.