如何在存储过程中连接 [英] how to concatenate in stored procedure

问题描述

如何在存储过程中连接



我想在一个表中将P101保存为id，并且每次插入时自动增加像P101，P102，P103这样的数字数据这个p101应该自动增加



这是我的程序



i想在Monitor_ID中保存P101，P102和Qty_Device_ID列

how to concatenate in stored procedure

I want to save P101 as id in One table and auto increment only number like P101,P102,P103 with every insertion of data this p101 should auto increment

this is my procedure

i want to save P101, P102 in Monitor_ID and Qty_Device_ID columns

create Procedure [dbo].[pro_Monitor]
(

  @Monitor_ID varchar(50),
  @Monitor_Name varchar(50),
  @Monitor_model varchar(50),
  @Monitor_O_N int ,
  @Qty_Device_ID varchar,
  @Qty_Recived bigint
  

)

As
Begin
insert into dbo.monitor (Monitor_ID ,Monitor_Name,Monitor_date,Monitor_model,Monitor_O_N )
values (@Monitor_ID ,@Monitor_Name,getdate(),@Monitor_model,@Monitor_O_N);

insert into dbo.Tbl_ReceivedQty(Qty_Device_ID,Qty_Received)
values (@Qty_Device_ID,@Qty_Recived);
end

推荐答案

您好，



Sql Server 2012引入了 SEQUENCE 对象，允许您生成与任何表无关的顺序数值。您可以定义如下所示的序列。

Hello,

Sql Server 2012 has introduced SEQUENCE objects, which allow you to generate sequential numeric values not associated with any table. You can define a sequence as shown below.

CREATE SEQUENCE Schema.SequenceName
AS int
START WITH 1
INCREMENT BY 1

假设你已经定义了一个名为的序列MonitorSeq然后您可以在存储过程中使用以下语法来获取下一个值。

So lets say you have defined a sequence named "MonitorSeq" then you can use following syntax in your stored procedure to get the next value.

DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR Schema.MoniterSeq;

请不要忘记改变架构到您的实际架构名称



问候，

Please don''t forget to change Schema to your actual Schema Name

Regards,

hi，



试试这样...

Try like this...

create Procedure [dbo].[pro_Monitor]
(
  @Monitor_Name varchar(50),
  @Monitor_model varchar(50),
  @Monitor_O_N int ,
  @Qty_Device_ID varchar,
  @Qty_Recived bigint

 )
 
As
Begin
DECLARE @Monitor_ID varchar(50)=NULL
-- Get the next Monitor count 
SELECT @Monitor_ID ='P'+CAST(NextValue AS VARCHAR(10)) FROM sysPeram WHERE Code='Monitor'
SELECT @Qty_Device_ID=@Monitor_ID 

insert into dbo.monitor (Monitor_ID ,Monitor_Name,Monitor_date,Monitor_model,Monitor_O_N )
values (@Monitor_ID ,@Monitor_Name,getdate(),@Monitor_model,@Monitor_O_N);
 
insert into dbo.Tbl_ReceivedQty(Qty_Device_ID,Qty_Received)
values (@Qty_Device_ID,@Qty_Recived);

-- Update System Peram table 
UPDATE sysPeram SET NextValue=NextValue+1 WHERE Code='Monitor'
end

GVPrabu

GVPrabu

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