January 6, 2014 at 4:04 am
Hi All,
I have a stored procedure like below.
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.
January 6, 2014 at 4:16 am
Your procedure is fetching the greatest billNo from the table and just adding 1 to it. There's no logic anywhere in that procedure to set the bill number for a new year
Simplified:
SELECT @OUT=MAX(BillNo) from IPTRANS
SELECT @OUT=@OUT+1
If you want the procedure to return something other than the highest bill in the table +1, you need to change the code, or manually add a fake bill number to the table so that the MAX(BillNo)+1 logic does what you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2014 at 6:07 am
p.avinash689 (1/6/2014)
Hi All,I have a stored procedure like below.
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.
Within your code you use the @Y and @D variables to hold a year-specific value. The @Y variable is filled but not used in any other place. Your @D variable is filled but only used if no existing bill numbers exists.
Like Gail suggested you can manually add a new bill no. but next year you will run into the same problem. The best way is to alter your code. Below I have added one example allthough other solutions are also possible.
...
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 LEFT(CONVERT(VARCHAR,@OUT), 5) <> LEFT(@D, 5)
SELECT @OUT=CONVERT(NUMERIC,@D)+1
ELSE
SELECT @OUT=@OUT+1
SELECT @BILLNO=@OUT
END
January 6, 2014 at 6:18 am
HanShi (1/6/2014)
p.avinash689 (1/6/2014)
Hi All,I have a stored procedure like below.
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.
Within your code you use the @Y and @D variables to hold a year-specific value. The @Y variable is filled but not used in any other place. Your @D variable is filled but only used if no existing bill numbers exists.
Like Gail suggested you can manually add a new bill no. but next year you will run into the same problem. The best way is to alter your code. Below I have added one example allthough other solutions are also possible.
...
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=0LEFT(CONVERT(VARCHAR,@OUT), 5) <> LEFT(@D, 5)SELECT @OUT=CONVERT(NUMERIC,@D)+1
ELSE
SELECT @OUT=@OUT+1
SELECT @BILLNO=@OUT
END
Or just uncomment the commented out where clause. If the where clause is added back to the query, it should do what is required.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2014 at 5:15 am
thank you all i resolved it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply