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
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:
You get the ComplaintID on the form M1, M2, M3 etc, not M001, M002 etc.
answered Jan 10, 2012 at 11:39 PM
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.
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.