New answers tagged dml
An identity field is meant to be a surrogate key field. The fact that it is the PK assures uniqueness, not necessarily the identity itself. No meaning should ever be assigned the value of a surrogate key. That means you can assume nothing about the associated row, not even the sequence when it was inserted into the table. That is, you should not assume id ...
Rather than calculate the value in code, consider a computed column instead. ALTER TABLE dbo.Customers DROP COLUMN Id; ALTER TABLE dbo.Customers ADD Id AS (CASE CustomerType WHEN 1 THEN N'P' ELSE N'x' END + CAST((SequenceId + 1000000) AS nvarchar(10))); Below is an example of the insert proc using this computed column: CREATE PROCEDURE ...
You never set the SequenceId to anything. It is not possible to set the value of a column during an insert using an expression that references the value of an IDENTITY column on that same table that will be set upon that same insert operation. If you think about it this makes sense because the row doesn't exist until you insert it, so there is no IDENTITY ...
SequenceID is invalid,since you are not specifying from where to get the value.I suggest,create one more temp variable and add ,some thing like below declare @seqid int select @seqid =next value for from your sequence then in place of sequence ,keep this variable
Top 50 recent answers are included