插入组合字符串和日期 [英] insert cobination string and date

问题描述

大家好

我想使用这样的存储过程在我的coloum中插入代码:BCA110000000001
其中BCA是来自用户输入的代码
现在是11岁

解决方案

让我测试一下:

 创建 过程 InsertData  @代码  varchar ( 50 )
目标
开始
    声明  @ year   varchar ( 2 )
    声明  @ number   int 
    声明  @ formatedNumber   varchar ( 20 )
    声明  @ value   varchar ( 50 )
    选择 @ number = MAX(CAST(右(code， 10 ) As   int ))) from  TableName
    设置 @ number = ISNULL( @ number ， 0 )+  1 
    设置 @ formatedNumber = RIGHT('  000000000' + CAST( @ number   as   varchar  ( 10 ))， 10 )
    设置 @ year = RIGHT(CAST(YEAR(GETDATE()) As   4 ))，设置 @ value = @ code +  @ year  +  @ formatedNumber 
    插入 插入 TableName(code)值(  @值)
结束 

FLetz假定键列(BCA11000000001)的名称为DataKey. Letz将表创建为:

 创建  TABLE  dbo.MyTable
(
    DataKey  int  否  NULL  主要  KEY ，
    OtherKey  varchar ( 50 )，
    ....
)

SQL将从1开始将DataKey列保留为标识主列.预期的键使用SELECT命令构造为:

  SELECT  '  BCA'  + substring(year(getdate())， 1 ， 2 )+ Cast(数据键 as   varchar ( 10 ) 



它将得到预期的代码BCA11000000001 

您可以检查以下存储过程

CREATE PROCEDURE [dbo].[Check]
	@UserInput nvarchar(50),
	@Last int
AS
DECLARE @Result nvarchar(20)
DECLARE @pad_characters VARCHAR(10)



SET @pad_characters = ''0000000000''


SET @Result = @UserInput+SUBSTRING(convert(nvarchar,Datepart(Year,getdate())) ,3 ,2)+ RIGHT(@pad_characters+convert(nvarchar(10),@Last),10)


SeLECT @Result

Exec [dbo].[Check] ''BCA '',1

希望对您有所帮助，
Theingi Win

hello all

i want to insert code in my coloum using store procedure like this : BCA110000000001
where BCA is code from user input
11 is year now

how i can do this??

解决方案

let test this:

Create Procedure InsertData @code varchar(50)
As
Begin
    Declare @year varchar(2)
    Declare @number int
    Declare @formatedNumber varchar(20)
    Declare @value varchar(50)
    Select @number=MAX(CAST(RIGHT(code,10) As int)) from TableName
    Set @number=ISNULL(@number,0) + 1
    Set @formatedNumber=RIGHT('000000000'+CAST(@number as varchar(10)),10)
    Set @year=RIGHT(CAST(YEAR(GETDATE()) As varchar(4)),2)
    Set @value=@code + @year + @formatedNumber
    Insert into TableName(code) values(@value)
End

FLetz assume the key column (BCA11000000001) name as DataKey. Letz create the table as:

CREATE TABLE dbo.MyTable
(
    DataKey int NOT NULL PRIMARY KEY,
    OtherKey varchar(50),
    ....
) 

SQL maintains DataKey column as identity primary column, starting from 1. Expected key is constructed using SELECT command as:

SELECT 'BCA'+ substring(year(getdate()),1,2) + Cast(Datakey as varchar(10)

It will result the expected code BCA11000000001

You can check the following store procedure

CREATE PROCEDURE [dbo].[Check]
	@UserInput nvarchar(50),
	@Last int
AS
DECLARE @Result nvarchar(20)
DECLARE @pad_characters VARCHAR(10)



SET @pad_characters = ''0000000000''


SET @Result = @UserInput+SUBSTRING(convert(nvarchar,Datepart(Year,getdate())) ,3 ,2)+ RIGHT(@pad_characters+convert(nvarchar(10),@Last),10)


SeLECT @Result

Exec [dbo].[Check] ''BCA '',1

Hope be helpful,
Theingi Win

这篇关于插入组合字符串和日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！