如何创建存储过程SQL将数字存储在数据库中，如0001-0002-0010-0011 -9999 [英] How To Create Stored Procedure SQL To Store Number in database like 0001-0002-0010-0011 -9999

问题描述

USE [RealEstate]

GO

/ ******对象：StoredProcedure [dbo]。[SP_AutoNumber]脚本日期：17/04/2014 12 ：00：31 AM ****** /

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO







ALTER PROCEDURE [dbo]。[SP_AutoNumber]

@code int，

@MaxValue int，

@LastValue int，

@strGUID uniqueidentifier



AS



（从RlRegistrationRealEstate中选择@MaxValue = max（RealEstateNumber））



如果@MaxValue为null，则设置为@MaxValue ='0001'set @LastValue = right（@ MaxValue，3）+ 1 return right（'000'+ convert（varchar（4），@ lastValue），4）set @ MaxValue = @MaxValue + @LastValue





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

USE [RealEstate]
GO
/****** Object: StoredProcedure [dbo].[SP_AutoNumber] Script Date: 17/04/2014 12:00:31 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[SP_AutoNumber]
@code int,
@MaxValue int,
@LastValue int ,
@strGUID uniqueidentifier

AS

(select @MaxValue = max(RealEstateNumber) from RlRegistrationRealEstate)

if @MaxValue is null set @MaxValue = '0001' set @LastValue = right(@MaxValue,3) + 1 return right('000' + convert(varchar(4),@lastValue),4) set @MaxValue= @MaxValue + @LastValue


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

推荐答案

不要。

将它存储为单独的数字并召集当你检索信息时，rt那些字符串用于演示。

Don't.
Store it as separate numbers and convert those to a string for presentation when you retrieve the information.

使用SQL Server自定义自动生成的序列 [ ^ ]



自定义。

Custom Auto-Generated Sequences with SQL Server[^]

Customize it.

试试这个例子......我已经使用了同样的要求;

try this example...already i used this same requirement;

ALTER PROCEDURE [dbo].[SP_AutoNumber]
@strGUID uniqueidentifier
AS
BEGIN
	
	SET NOCOUNT ON;
   
if exists(select * from TABLENAME where GUID = @strGUID)
begin
SELECT RIGHT(REPLICATE('0', 4) + CAST(CAST(COALESCE(MAX(columnname), 0) AS INT)  AS VARCHAR), 4) dcno
  FROM
(
  SELECT TOP 1 columnname[generatecolumn]
    FROM Tablename
   ORDER BY sno DESC
) q;
end
else



begin

SELECT RIGHT(REPLICATE('0', 4) + CAST(CAST(COALESCE(MAX(columnname), 0) AS INT) + 1 AS VARCHAR), 4) dcno
  FROM
(
  SELECT TOP 1 columnname[generatecolumn]
    FROM tablename
   ORDER BY sno DESC
) q;

end
END

这篇关于如何创建存储过程SQL将数字存储在数据库中，如0001-0002-0010-0011 -9999的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！