Click here to Skip to main content
15,077,641 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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
Posted
Updated 10-Jan-12 21:29pm
v3
Comments
Abhinav S 10-Jan-12 23:28pm
   
What have you tried so far? Are you running into some issues with your code?

Hi,

You can create 3 sequences

1.SEQ_mgmt
2.SEQ_Netwrk
3.SEQ_otrs

like below syntax

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



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.
SQL
INSERT INTO members
(complaintcode)
VALUES ( 'M' || ltrim (to_char (customer_seq.NEXTVAL,0009));



See example to use sequences:
SQL
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.
   
v2
Comments
Tejas Vaishnav 11-Jan-12 4:22am
   
I think it will return the integer value like 1,2,3,4,5.... but that person want
M001 as id so it will be string type in that the SEQUENCE not work
Tejas Vaishnav 11-Jan-12 4:23am
   
please read the question properly and then answer...
manognya kota 11-Jan-12 4:38am
   
Hi Tejas,

3sequences can be created and be concatenated with M for management, N for netwrk and the other being normal sequence.

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

Hope its clear now.
ravivangala 13-Jan-12 7:50am
   
i think i will return as
M001
N002
but the code sholud be like
M001
N001
manognya kota 13-Jan-12 9:28am
   
thats the reason we have 3 sequences.
when its management use
INSERT INTO members
(complaintcode)
VALUES ( 'M' || ltrim (to_char (SEQ_mgmt.NEXTVAL,'009'));

When its network
INSERT INTO members
(complaintcode)
VALUES ( 'N' || ltrim (to_char (SEQ_Netwrk.NEXTVAL,'009'));

and when others,

INSERT INTO members
(complaintcode)
VALUES ( ltrim (to_char (SEQ_otrs.NEXTVAL,'0009'));

Hope its clear now.
manognya kota 11-Jan-12 4:39am
   
I suggested for 3 sequences so that the order will not be affected.
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...
   
Comments
ravivangala 10-Jan-12 23:57pm
   
yes i want to insert complaintdate and IpAdress,ProblemDesc,Resp
then system need to generate complaintid automatically if i give responce is management like that M001 OR IF I GIVE responce is networkperson it should generate N001 AND IF I GIVE OTHERS TO RESPONCE FIELD IT SHOULD GENERATE 0001 AS COMPLAINT ID....PLEASE HELP ME REGARDING THIS....
SQL
/* 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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900