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

ravisavitri gravatar image

13 2 2 2

(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

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.4k 16 19 33

(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

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 689 times

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