用于生成序列号的存储过程 [英] Store Procedure for generating Serial number

问题描述

大家好，

我必须创建一个商店程序，我将用户输入作为客户型号，客户订单号，订单型号和订单数量。

现在我必须在我的数据库中检查此客户型号否，客户订单号，订单型号是否存在，如果不是那么我必须创建如下

Foe New Customer ，输入值为 -

客户型号 - A001

客户订单号 - Ord200

订购型号 - 311A

订单数量10

所以我的商店程序应该生成如311A-01,311A-02,311A-03，...........，

311A-09,311A-10并存储在数据库中

Hi All,
I have to create a Store Procedure where I am taking user input as Customer Model No, Customer Order No, Order model and Order Qty.
Now I have to check in my database this Customer Model No, Customer Order No, Order model existed if it is not then I have to create like below
Foe New Customer, Input value is-
Customer Model No - A001
Customer Order No - Ord200
Order model - 311A
Order Qty -10
so here my Store Procedure should generate like 311A-01,311A-02,311A-03,...........,
311A-09,311A-10 and store in Data Base

CREATE PROCEDURE OrderNo @CusModlNo nchar, @CusOrdNo nchar,  @OrderModelNo nchar, @OrderQty int
 AS 
BEGIN
Declare @OrderNo int

END
GO

谢谢

Thanks

推荐答案

//重写程序如下所示

//也检查列数据类型是nc​​har还是其他东西，即nvarchar或varchar，它的大小

//Rewrite procedure like given below
//also check column datatype is it nchar or something else i.e. nvarchar or varchar with it''s size

CREATE PROCEDURE OrderNo 
(
   @CusModlNo nchar, 
   @CusOrdNo nchar,  
   @OrderModelNo nchar, 
   @OrderQty int
)
 AS
BEGIN
   --Write insert query as per your table name and column name i.e.
   insert into TableName values(@CusModlNo, @CusOrdNo, @OrderModelNo, @OrderQty) ;
END
GO

如果客户型号 - A001

客户订单号 - Ord200

订单数量10

订购型号 - 311A //如果它是311A-01,311A-02 ......其他数据会相同...

if Customer Model No - A001
Customer Order No - Ord200
Order Qty -10
Order model - 311A //if it will 311A-01, 311A-02... and other data will same then...

CREATE PROCEDURE OrderNo 
(
   @CusModlNo nchar, 
   @CusOrdNo nchar,  
   @OrderModelNo nchar, 
   @OrderQty int
)
 AS
BEGIN
   with nums as
   (select '-01' as srno union all
    select '-02' as srno union all
    select '-031' as srno union all
    select '-04' as srno union all
    select '-05' as srno 
    )
   --Write insert query as per your table name and column name i.e.
   insert into TableName 
        select @CusModlNo, @CusOrdNo + srno, @OrderModelNo, @OrderQty) 
        from nums;
END
GO

快乐编码！

：）

Happy Coding!
:)

这篇关于用于生成序列号的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！