asked
stored procedure creation.....
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
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)) GO CREATE PROC InsertComplaint(@CompDate datetime, @ipadress varchar(50), @problemdesc nvarchar(max), @resp varchar(30)) AS INSERT INTO Complaint (ComplaintID, CompDate, IpAdress, Problemdesc, Resp) SELECT 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)) ,@CompDate, @ipadress, @problemdesc, @resp GO You get the ComplaintID on the form M1, M2, M3 etc, not M001, M002 etc.
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. DECLARE @MaxId INT BEGIN TRAN SELECT @MaxId = MAX(CAST(RIGHT(ComplaintID,LEN(ComplaintID) - 1) AS INT)) + 1 FROM Complaint WITH (UPDLOCK, HOLDLOCK) WHERE Resp = @resp SET @MaxId = ISNULL(@MaxId, 1) INSERT INTO Complaint (ComplaintID, CompDate, IpAdress, Problemdesc, Resp) SELECT CASE @resp WHEN 'Management' THEN 'M' WHEN 'Network Person' THEN 'N' ELSE 'O' END + CAST(@MaxId AS VARCHAR(10)) ,@CompDate, @ipadress, @problemdesc, @resp COMMIT TRAN 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. EDIT: 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] ( [CompDate] ,[ipadress] ,[problemdesc] ,[resp] ) 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]
