关于存储过程 [英] regarding stored procedure

问题描述

您必须从sql server中创建兼容的寄存器表
符合标准(CompliantID，Comp.Date，IpAdress，ProblemDesc，Resp)

合规ID将自动生成为...如果是响应.是从M001(CompliantID)开始的管理方式..
如果网络负责人以N001(CompliantID)开头，则其他人负责以O001(CompliantID)开头.在存储过程的帮助下

You have to make a compliant register table from sql server
Compliants(CompliantID,Comp.Date,IpAdress,ProblemDesc,Resp)

Compliant ID to be generated automatically as… If Resp. is Management Start with M001(CompliantID) like that..
If Network Person is responsible start with N001(CompliantID) if Others are responsible start with O001(CompliantID) like that. with the help of stored procedure

推荐答案

您可以创建3个序列

1.SEQ_mgmt
2.SEQ_Netwrk
3.SEQ_otrs

像下面的语法

Hi,

You can create 3 sequences

1.SEQ_mgmt
2.SEQ_Netwrk
3.SEQ_otrs

like below syntax

<pre lang="sql">CREATE SEQUENCE sequence_name
 [INCREMENT BY #]
 [START WITH #]
 [MAXVALUE # | NOMAXVALUE]
 [MINVALUE # | NOMINVALUE]
 [CYCLE | NOCYCLE]</pre>

在您的程序中，如果负责人是管理人员，则插入SEQ_mgmt，
如果其网络插入SEQ_ntwrk，则其网络插入SEQ_otrs.

相应地插入


与M进行管理并与N进行网络连接.

in your procedure if the responsible person is management insert with SEQ_mgmt,
if its network insert with SEQ_ntwrk, if its others insert with SEQ_otrs.

insert accordingly


concatenate with M for management and N for network.

INSERT INTO members
(complaintcode)
VALUES ( 'M' || ltrim (to_char (customer_seq.NEXTVAL,0009));

请参见使用序列的示例:

See example to use sequences:

To create the sequence:
CREATE SEQUENCE customer_seq INCREMENT BY 1 START WITH 100

To use the sequence to enter a record into the database:
INSERT INTO customer (cust_num, name, address)
VALUES (customer_seq.NEXTVAL, 'John Doe', '123 Main St.')

To find the value just entered into the database:
SELECT customer_seq.CURVAL AS LAST_CUST_NUM

希望这对您有帮助.

Hope this helps.

首先，您说Compliant ID是Auto Generated，因此需要使用存储过程执行的操作需要更新该字段或插入根据您的描述提交的文件，例如管理然后是M001等.


未使用INSERT查询插入自动生成的文件，它将由DATABASE SERVER自动插入"

因此，请对您的问题感到满意，或对您的问题给出适当的详细信息...

First of all you said that the Compliant ID is Auto Generated, so what you need to do using store procedure you need to update that field or you need to insert that filed as per your description like if Management then M001 etc..


"Auto Generated Filed are not inserted using INSERT query it will automatically inserted by DATABASE SERVER"

SO be soure about your question or give proper detail of your problem...

/* HERE I CREATED DUMMY TABLE FOR DEMONSTRATING YOU
YOU NEED TO APPLY THE SP LOGIC AS PER YOUR REQUIREMENT
*/

CREATE TABLE Testing(
	ID VARCHAR(5),
	NAME VARCHAR(MAX),
	DESCP VARCHAR(MAX)
);
GO

/* HERE YOU NEED TO PASS A VALUE TO THIS SP LIKE THIS
	@NAME = 'MANAGEMENT'
	@DESCP = 'YOUR MANAGEMENT DESCRIPTION'
	
	@NAME = 'NETWORK'
	@DESCP = 'YOUR NETWORK DESCRIPTION'
	
	IT WILL AUTOMATICALLY INSERT THE RECORD
	IT YOU GO FOR MANAGEMENT THEN THE ID WILL BE
	M001 FOR FIRST TIME then NEXT TIME IT WILL M002
	AND THEN IF YOU GO FOR NETWORK THEN THE ID WILL BE
	N001 FOR FIRST NETWORK RECORD THEN N002...
	
	I THINK YOU WANTED THIS M I RIGHT..? OR RIGHT..?	
*/	

CREATE PROCEDURE SP_INSERT
	@NAME VARCHAR(MAX),
	@DESCP VARCHAR(MAX)
AS
BEGIN
	/* Logic for Getting New ID as Per the NAME with PRE FIX */
	DECLARE @NEWID VARCHAR(5);
	DECLARE @PREFIX VARCHAR(1);
	SET @PREFIX = UPPER(SUBSTRING(@NAME, 1, 1))
	SELECT @NEWID = (@PREFIX + replicate('0', 3 - len(CONVERT(VARCHAR,N.OID + 1))) + CONVERT(VARCHAR,N.OID + 1)) FROM (
	SELECT CASE WHEN MAX(T.TID) IS null then 0 else MAX(T.TID) end as OID FROM (
	SELECT SUBSTRING(ID, 1, 1) as PRE_FIX,SUBSTRING(ID, 2, LEN(ID)) as TID FROM Testing
	) AS T WHERE T.PRE_FIX = @PREFIX
	) AS N

	/* INSERT QUERY FOR NEW RECORD */
	INSERT INTO Testing VALUES (@NEWID,@NAME,@DESCP)
END
GO

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