Click here to Skip to main content
15,919,613 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 6-Jan-14 0:09am
v2
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

@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...
 
Share this answer
 
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.
 
Share this answer
 
v2
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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900