关于存储过程中的日期更新 [英] regarding date update in stored procedure

问题描述

大家好，



我有一个如下存储过程。

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

问题是年份没有更新。

现在我们在2014年，但它产生了账单没有与2013年唯一。

Ex bill no是：520140123 ---我想这样但我只是喜欢520130123.



我如何解决这个问题。

任何人都可以提出建议来克服这个问题。

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.

解决方案

@D获得正确的2014年价值，但你从未使用它因为这个

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

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

IF @OUT=0

如果您在IPTRANS中甚至有一条记录，您永远不会使用@D来计算新账单号...

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

SELECT @OUT=MAX(BillNo) from IPTRANS

以上声明选择520130122为@OUT变量

然后是Else条件执行然后它给出一个输出：520130122 + 1



首先想想你想要的，相应地编写代码。

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.

来自IPTRANS的SELECT @ OUT = MAX（BillNo）



这是在这里完成的唯一实际数据库操作。因此，这是proc应该做的唯一事情，其余的应该在你的程序代码中完成。否则，业务规则将隐藏在数据库中，因此更难以调试。我能想到的唯一例外是，如果您想部署代码，并且能够在不更改代码库的情况下更改此过程。即便如此，对我来说似乎有点疯狂。我无法弄清楚你想要做什么，或者它是如何理解的。

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.

这篇关于关于存储过程中的日期更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！