如何创建存储过程自动在表varchar（）中生成数字0001-9999存储 [英] How to Create Stored Procedure Auto Generate Number 0001-9999 store in the table varchar()

问题描述

  USE  [RealEstate] 
  GO  
 
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
  ALTER   procedure  [dbo]。[generate] 
 
  as  
 
 声明  @ MaxValue   nvarchar （ 20 ）
 声明  @ LastValue   nvarchar （ 20 ）
 声明  @ strGUID   uniqueidentifier  
 
 选择  @ MaxValue  = max（RealEstateNumber）来自<如果  @ MaxValue     null   set   @ MaxValue  = '  0001' 
  else  
  set   @ MaxValue  = convert（ int ，正确（ @ MaxValue ， 4 ））+  1  
 
 
  set   @ MaxValue  =  @ MaxValue  +  @ LastValue  
 
  UPDATE  [dbo] .RlRegistrationRealEstate  SET  RealEstateNumber = @ MaxValue  WHERE  GUID = @ strGUID 

when你在dataBase显示记录数量中插入新记录像这样0002-0003-9999

解决方案

Hello Hay，

请参考此

http://www.w3schools.com/sql/sql_autoincrement.asp [< a href =http://www.w3schools.com/sql/sql_autoincrement.asptarget =_ blanktitle =新窗口> ^ ]



一切顺利：）

首先，让我说我只是'要明白做事的实际原因 这个。这可能会在将来给您带来一些问题。你确定需要这个吗？



您是否考虑将值存储为自动递增的整数而不是varchar（并且可能使用0001-9999格式仅用于显示目的）？



无论如何，我将向您展示如何做到这一点。



这是我的样本：

  DECLARE  < span class =code-sdkkeyword> @ Table   TABLE （[Value]  VARCHAR （  4 ））; 
  DECLARE   @ NewValue   INT  ; 
 
  INSERT   INTO   @ Table （[Value]）
  VALUES （'  0001'），（'  0009'）; 
 
  SET   @ NewValue  =（ SELECT  ISNULL（MAX（ CONVERT （ INT ，[Value]））， 0 ）+  1   FROM   @表）; 
 
  INSERT   INTO   @ Table （[Value]）
  VALUES （REPLICATE（'  0'，（ 4   -  LEN（ @ NewValue ）） ）+  CONVERT （ VARCHAR （ 4 ） ， @ NewValue ））; 

注意：只应将数值存储在列中值。

给定值为'0001'和'0009'。如您所见，我得到一个整数的新值。然后我使用 REPLICATE（string_expression，integer_expression）函数重复指定次数'0'的值。



应该有三个表中的记录：'0001'，'0009'和新生成的'0010'。让我们检查一下：

  SELECT  *  FROM   @ Table ; 

 值 
 0001 
 0009 
  0010  

您的存储过程可能看起来像这样：

  USE  [RealEstate] 
  GO  
 
  SET   ANSI_NULLS   ON  
  GO  
 
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
  ALTER   PROCEDURE  [dbo]。[生成] 
  AS  
  BEGIN  
  DECLARE   @ NewValue   INT ; 
  DECLARE   @ strGUID   UNIQUEIDENTIFIER  ; 
 
  SET   @ NewValue  =（ SELECT  ISNULL（MAX（ CONVERT （ INT ，[RealEstateNumber]））， 0 ）+  1   FROM  [dbo]。[ RlRegistrationRealEstate]）; 
 
 更新 [dbo]。[RlRegistrationRealEstate] 
  SET  [ RealEstateNumber] =（REPLICATE（'  0'，（ 4   -  LEN（ @ NewValue ）））+  CONVERT （ VARCHAR （ 4 ）， @ NewValue ））
  WHERE  GUID =  @ strGUID ; 
  END  

顺便说一下，你没有为<设置任何值 @strGUID 变量！





------ -------------------------------------------------- -----------------

附录：

SQL Server 2012 你可以使用 FORMAT 功能代替 REPLICATE 。

这是一个简单的例子：

< pre lang =SQL> DECLARE @ NewTable TABLE （[intValue] INT ）;

INSERT INTO @ NewTable （[intValue]）
VALUES （ 1 ），（ 9 ）;

SELECT FORMAT（[intValue]，' 0000'） AS [varcharValue] FROM @ NewTable ;

  varcharValue  
 0001 
 0009 

USE [RealEstate]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[generate]

 as

  Declare @MaxValue nvarchar(20)
  Declare @LastValue nvarchar(20)
  Declare @strGUID uniqueidentifier

select @MaxValue = max(RealEstateNumber) from RlRegistrationRealEstate

if @MaxValue is null set @MaxValue = '0001'
else
set @MaxValue =convert(int,right(@MaxValue,4)) + 1


  set @MaxValue = @MaxValue + @LastValue

UPDATE [dbo].RlRegistrationRealEstate SET RealEstateNumber=@MaxValue WHERE GUID =@strGUID

when you insert new record in the dataBase Display Number Of Record Like this 0002-0003-9999

解决方案

Hello Hay,
pls refer this
http://www.w3schools.com/sql/sql_autoincrement.asp[^]

All the best:)

Hi,

First of all, let me say that I just don't understand a practical reason for doing this. This may cause you some problems in the future. Are you sure that you need this?

Have you considered storing your values as an auto incremented integer instead of varchar (and maybe using "0001-9999" format for display purposes only)?

Anyway, I am going to show you how this could be done.

Here's my sample:

DECLARE @Table TABLE ([Value] VARCHAR(4));
DECLARE @NewValue INT;
 
INSERT INTO @Table ([Value])
VALUES ('0001'), ('0009');
 
SET @NewValue = (SELECT ISNULL(MAX(CONVERT(INT, [Value])), 0) + 1 FROM @Table);
 
INSERT INTO @Table ([Value])
VALUES (REPLICATE('0', (4 - LEN(@NewValue))) + CONVERT(VARCHAR(4), @NewValue));

Note: Only numeric values should be stored in the column "Value".
Given values are '0001' and '0009'. As you can see, I am getting a new value as integer. Then I am using the REPLICATE ( string_expression ,integer_expression ) function to repeat '0' value specified number of times.

There should be three records in the table: '0001', '0009' and newly generated '0010'. Let's check it:

SELECT * FROM @Table;

Value
0001
0009
0010

Your stored procedure could look something like this:

USE [RealEstate]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[generate]
AS
  BEGIN
      DECLARE @NewValue INT;
      DECLARE @strGUID UNIQUEIDENTIFIER;
 
      SET @NewValue = (SELECT ISNULL(MAX(CONVERT(INT, [RealEstateNumber])), 0) + 1 FROM [dbo].[RlRegistrationRealEstate]);
 
      UPDATE [dbo].[RlRegistrationRealEstate]
      SET [RealEstateNumber] = (REPLICATE('0', (4 - LEN(@NewValue))) + CONVERT(VARCHAR(4), @NewValue))
      WHERE GUID = @strGUID;
  END

By the way, you're not setting any value for @strGUID variable!


-------------------------------------------------------------------------
Addendum:
In SQL Server 2012 you can use the FORMAT function instead of the REPLICATE.
Here's the simple example:

DECLARE @NewTable TABLE ([intValue] INT);

INSERT INTO @NewTable ([intValue])
VALUES (1), (9);

SELECT FORMAT ([intValue], '0000') AS [varcharValue] FROM @NewTable;

varcharValue
0001
0009

这篇关于如何创建存储过程自动在表varchar（）中生成数字0001-9999存储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！