regarding stored procedure

Question

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 10-Jan-12 17:26pm

ravivangala

Updated 10-Jan-12 21:29pm

Mehdi Gholam

v3

Add a Solution

Comments

Abhinav S 10-Jan-12 23:28pm

What have you tried so far? Are you running into some issues with your code?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

manognya kota · Answer 1 · 2012-01-10T21:44:00

Solution 3

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.

Posted 10-Jan-12 21:44pm

manognya kota

Updated 10-Jan-12 22:54pm

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.

Tejas Vaishnav · Answer 2 · 2012-01-10T17:48:00

Solution 1

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...

Posted 10-Jan-12 17:48pm

Tejas Vaishnav

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....

Tejas Vaishnav · Answer 3 · 2012-01-10T21:24:00

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

regarding stored procedure

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us