Problem with identity in sqlserver 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with identity in sqlserver 2005

Author

Topic

haneesh
Starting Member

7 Posts

Posted - 2009-03-11 : 05:00:12

i have a procedure that insert the valuese in department table.
Table has 3 fields like deptid,deptname,deptstatus.
dept id(pk) and its identity true, and deptstatus has checkconstraint.
that can hold the values only "D" or "A" .

My problem is that when i execute the procedure for insertion with deptstatus other than "A" &"D"( ie. check constraint violation occurs), then identity value will increment. How can i restrict this.

For eg: my current recod Id is 5.
when i execute procedure (constraint check violation occurs) with other than "A" or "D". shows the msg. NO insertion take place.
And if i again insert with correct value, then the recode id(deptid) is 7.
ie. previous id 5 and next 7. Number 6 is missing.
Lost the seiquence. How can i prevent this.

plz its urgent.

my procedure shown below:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sph_masterDepartmentIns]
-- Add the parameters for the stored procedure here
@VC_DEPTNAME varchar(30),
@C_STATUS char(1)
AS
BEGIN
DECLARE @retVal int
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
-- Insert statements for procedure here
IF(EXISTS( SELECT 1 FROM DEPARTMENT_MASTER WHERE VC_DEPTNAME=@VC_DEPTNAME))
BEGIN
SET @retVal=0
END
ELSE
BEGIN
INSERT INTO [DEPARTMENT_MASTER]
([VC_DEPTNAME]
,[C_STATUS])
VALUES
(@VC_DEPTNAME,
@C_STATUS)
SET @retVal=1
END

END TRY
BEGIN CATCH

SET @retVal=-1
IF @@TRANCOUNT > 0
Begin
ROLLBACK TRANSACTION;

end
END CATCH

IF @@TRANCOUNT > 0
COMMIT TRANSACTION;

END

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-11 : 05:29:27

The easiest way is to put an IF at the top of the Proc:

IF @C_STATUS in ('A','D')
begin
...
end
else
begin
select 'not correct'
end

I would ask why you needed it to be in sequence though? Does it really matter than much what value an identity is compared to the record before or after?!?

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-11 : 05:43:05

Y do you care about Gaps.??????

you can ressed this...... but this is not necessary.!!!!!

any way.
you need to add this statement after every insertion.

INSERT INTO table_name VALUES ('A')
if @@error <> 0
begin
declare @a int
select @a = max(ProductID) from table_name
DBCC CHECKIDENT ('table_name', RESEED,@a)
end

Try this but only less hope.

Karthik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources