stored procedure creation.....

You have to make a compliant register table from sql server


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

more ▼

asked Jan 10, 2012 at 08:49 PM in Default

avatar image

13 2 2 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I'm not too fond of your database design - you store redundant information (in Resp and as first letter of CompliantID).

But this is how it could be done:

 CREATE TABLE Complaint (ComplaintID varchar(10) primary key, CompDate datetime  ,IpAdress varchar(50),ProblemDesc nvarchar(max),Resp varchar(30))
 CREATE PROC InsertComplaint(@CompDate datetime, @ipadress varchar(50), @problemdesc nvarchar(max), @resp varchar(30))
 INSERT INTO Complaint (ComplaintID, CompDate, IpAdress, Problemdesc, Resp)
     CASE @resp WHEN 'Management' THEN 'M' WHEN 'Network Person' THEN 'N' ELSE 'O' END +
         CAST(COALESCE((SELECT MAX(CAST(RIGHT(ComplaintID,LEN(ComplaintID)-1) AS int)) FROM Complaint WHERE Resp = @resp),0) +1 as varchar(10))

You get the ComplaintID on the form M1, M2, M3 etc, not M001, M002 etc.

more ▼

answered Jan 10, 2012 at 11:39 PM

avatar image

Magnus Ahlkvist
22.5k 20 43 43

(comments are locked)
10|1200 characters needed characters left

In adition to Mr. Magnus Ahlkvist's answer, I am never in favor of these kind of manual increments as they always lead to dreadful scenarios. For e.g. what if two inserts are executed at the same time? They would read the same MAX VALUE and will try to add the same value. In case the ComplaintID would be the primary/unique key, one insert would fail. Otherwise two complaints with same ID would be lodged which wont be ideal either. To overcome this you would need to bound the script in TRANSACTION MODE and using SELECT WITH (UPDLOCK, HOLDLOCK) i.e.


 SELECT @MaxId = MAX(CAST(RIGHT(ComplaintID,LEN(ComplaintID) - 1) AS INT)) + 1
 SET @MaxId = ISNULL(@MaxId, 1)
 INSERT INTO Complaint (ComplaintID, CompDate, IpAdress, Problemdesc, Resp)
 CASE @resp WHEN 'Management' THEN 'M' WHEN 'Network Person' THEN 'N' ELSE 'O' END +
     CAST(@MaxId AS VARCHAR(10))

But with this approach the deadlocks are ominous. So you should think again before implementing this kind of increment.

If you could let us know why you need such implementation, may be some of us could lead you to a better approach.


Another much better approach could be to use identity column + computed column. Then, you do not have to worry about the concurrency, duplicates, deadlocks etc. But this would require that you can live with M001, N002, M0003 sequence. Run below script to test it out.

 CREATE TABLE #Complaints
      AutoIncrementID INT IDENTITY(1, 1)
                          NOT NULL
                          PRIMARY KEY
     ,CompDate DATETIME
     ,ipadress VARCHAR(50)
     ,problemdesc NVARCHAR(MAX)
     ,resp VARCHAR(30)
     ,ComplaintId AS ( CASE resp WHEN 'Management' THEN 'M' WHEN 'Network Person' THEN 'N' ELSE 'O' END
                       + CASE WHEN AutoIncrementID > 999
                              THEN CONVERT(VARCHAR(50), AutoIncrementID)
                              ELSE RIGHT('000' + CONVERT(VARCHAR(50), AutoIncrementID),3)
                         END ) PERSISTED -- PERSISTED MAKES SENSE
 INSERT [#Complaints]
 SELECT CURRENT_TIMESTAMP, '', '', CASE WHEN [number] % 2 = 0 THEN 'Management'
                                     WHEN [number] % 3 = 0 THEN 'Others'
                                     ELSE 'Network Person' END
 FROM master.[dbo].spt_values
 SELECT * FROM [#Complaints] AS MC
 DROP TABLE [#Complaints]
more ▼

answered Jan 11, 2012 at 02:00 AM

avatar image

Usman Butt
14.6k 6 13 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 10, 2012 at 08:49 PM

Seen: 971 times

Last Updated: Jan 11, 2012 at 01:30 AM

Copyright 2018 Redgate Software. Privacy Policy