IDENTITY_INSERT is set to ON problem

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)

IDENTITY_INSERT is set to ON problem

Author

Topic

skaswani
Starting Member

24 Posts

Posted - 2008-10-19 : 11:08:35

Dear All,

=======================
SQLSERVER 2005
Windows XP
=======================

this is my Table Script
=============================

CREATE TABLE [dbo].[emp](
[empno] [int] IDENTITY(1,1) NOT NULL,
[ename] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[sal] [numeric](18, 0) NOT NULL,
[deptno] [int] NOT NULL
) ON [PRIMARY]

my procedure
=============

create proc insert_emp
@ename varchar(20),
@sal int,
@deptno int

as
SET IDENTITY_INSERT dbo.emp ON
insert into emp (ename,sal,deptno)
values ( @ename, @sal, @deptno)
select @@identity
SET IDENTITY_INSERT dbo.emp OFF

compile
==============

Command(s) completed successfully.

execute
==========

declare @ename varchar(25)
declare @sal int
declare @deptno int

exec insert_emp @ename='EE', @sal = 18000, @deptno =1

result / error
====================

Msg 545, Level 16, State 1, Procedure insert_emp, Line 8
Explicit value must be specified for identity column in table 'emp' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

please correct me where i am wrong!

thank you

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-10-19 : 11:25:58

Since you are not providing the identity value for the insert no need to set the option. Just insert:


insert into emp (ename,sal,deptno)
values ( @ename, @sal, @deptno)
select @@identity

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-10-19 : 11:30:33

To be clear, you would set identity insert on only in the case that you would like to provide the identity value in your insert statement.


SET IDENTITY_INSERT dbo.emp ON
insert into emp (empno, ename,sal,deptno)
values (@empno, @ename, @sal, @deptno)

Nathan Skerl

skaswani
Starting Member

24 Posts

Posted - 2008-10-19 : 11:55:47

thank you for replying, but then i witness some other problems also

i.e, if i modify my code like this

create proc insert_emp
@empno int,
@ename varchar(20),
@sal int,
@deptno int

as
SET IDENTITY_INSERT dbo.emp ON
insert into emp (empno,ename,sal,deptno)
values ( @empno, @ename, @sal, @deptno)
select @@identity
SET IDENTITY_INSERT dbo.emp OFF

when i execute

declare @empno int
declare @ename varchar(25)
declare @sal int
declare @deptno int

exec insert_emp @ename='EE', @sal = 18000, @deptno = 1

i get this error
===============

Msg 201, Level 16, State 4, Procedure insert_emp, Line 0
Procedure or function 'insert_emp' expects parameter '@empno', which was not supplied.

if i apply this
==================
declare @empno int
declare @ename varchar(25)
declare @sal int
declare @deptno int

exec insert_emp @empno=1 , @ename='EE', @sal = 18000, @deptno =1

it actully ever ride IDENTITY rule

and if i do

Msg 515, Level 16, State 2, Procedure insert_emp, Line 9
Cannot insert the value NULL into column 'empno', table 'employees.dbo.emp'; column does not allow nulls. INSERT fails.
The statement has been terminated.

please advice
Regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-19 : 13:05:56

you dont require the declare statements you just need only this

exec insert_emp @empno=1 , @ename='EE', @sal = 18000, @deptno =1

skaswani
Starting Member

24 Posts

Posted - 2008-10-19 : 13:47:02

visakh16 empno is a auto incremantal field
..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-19 : 13:52:18

quote:
Originally posted by skaswani

visakh16 empno is a auto incremantal field
..

so what. thats why you've IDENTITY INSERT ON. Didnt understand why you need declare statements. I was telling there's no need of them and so remove all.

skaswani
Starting Member

24 Posts

Posted - 2008-10-19 : 14:00:35

pls check my previous code..

create proc insert_emp
@empno int,
@ename varchar(20),
@sal int,
@deptno int

as
SET IDENTITY_INSERT dbo.emp ON
insert into emp (empno,ename,sal,deptno)
values ( @empno, @ename, @sal, @deptno)
select @@identity
SET IDENTITY_INSERT dbo.emp OFF

when i execute

exec insert_emp @ename='EE', @sal = 18000, @deptno = 1

i get this error
===============

Msg 201, Level 16, State 4, Procedure insert_emp, Line 0
Procedure or function 'insert_emp' expects parameter '@empno', which was not supplied.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-19 : 14:08:24

quote:
Procedure or function 'insert_emp' expects parameter '@empno', which was not supplied.

means:
your procedure 'insert_emp' expects parameter '@empno', which was not supplied by you.
This is true.

Planning replaces chance by mistake

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-19 : 14:08:51

thats why i told you to use this

exec insert_emp @empno=1 , @ename='EE', @sal = 18000, @deptno =1

skaswani
Starting Member

24 Posts

Posted - 2008-10-19 : 14:30:06

visakh16 if i set @empno=1 then there wont be any auto increment in empno field!

got it??

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-19 : 14:55:38

OK.
SET IDENTITY_INSERT dbo.emp ON --> means: please no auto increment, i want to insert the value myself.
SET IDENTITY_INSERT dbo.emp OFF -> means: please auto increment and i cannot insert the value myself.

OK?

Webfred

Planning replaces chance by mistake

skaswani
Starting Member

24 Posts

Posted - 2008-10-19 : 15:31:01

thank you so much, it really worked

i have a quick question,

what i want to do
====================
i want to check that if emplyee name (ename) exist in table i just get empno in return and somehow i could identify that a record is already found

and if not then insert row and auto increment

note:- i need to use this to vb.net

i tried this
but failed

create proc insert_emp
@ename varchar(20),
@sal int,
@deptno int

as
declare @v_empno int

if EXISTS (select @v_empno = empno
from Emp
where ename = @ename)

select v_empno
else
insert into emp (ename,sal,deptno)
values ( @ename, @sal, @deptno)
select @@identity

thanks,

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-10-19 : 15:58:45

create proc insert_emp
@ename varchar(20),
@sal int,
@deptno int

as

if EXISTS (select empno
from Emp
where ename = @ename)

select empno
from Emp
where ename = @ename

else
begin
insert into emp (ename,sal,deptno)
values ( @ename, @sal, @deptno)
select @@identity
end

If it still doesn't do what you want, explain what it's doing wrong.

--
Gail Shaw
SQL Server MVP

skaswani
Starting Member

24 Posts

Posted - 2008-10-19 : 16:14:34

how can a Guru be wrong:)

it worked,

i need to ask last 2 question on this issue

1) how will i get to know on vb.net forms using visual stdio that a record already exists
bec.. in both case i'll get a return value, i hope i make sence!

2) i like to know that is that good to use Store Procedure for performing DML operations or its better for just fetching records from DB?

thank you so much all!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 00:07:53

quote:
Originally posted by skaswani

how can a Guru be wrong:)

it worked,

i need to ask last 2 question on this issue

1) how will i get to know on vb.net forms using visual stdio that a record already exists
bec.. in both case i'll get a return value, i hope i make sence!
you may keep use a bit field in your stored procedure as an output parameter and returns the value based on if record was already found or not. then capture value of this bit field in vb.net code

2) i like to know that is that good to use Store Procedure for performing DML operations or its better for just fetching records from DB?
its better to use stored procedure when you consider maintainability. This ensures whenever you want to change code, you touch only stored procedure without touching application code each time. Also this segregation helps you to understand and identify in case of an error if its happening ion db side or is an application error. Also stored procedures have better reusability as you can use the same procedure wherever you need similar functionality. inline query requires you to repeat code on each place you want a particul;ar functionality.
thank you so much all!

see points in blue above

skaswani
Starting Member

24 Posts

Posted - 2008-10-20 : 00:58:03

thank you so much all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 01:23:39

quote:
Originally posted by skaswani

thank you so much all

welcome

skaswani
Starting Member

24 Posts

Posted - 2008-10-20 : 12:11:29

drop proc insert_emp

create proc insert_emp
@chk int output,
@ename varchar(20),
@sal int,
@deptno int

as

if EXISTS (select empno
from Emp
where ename = @ename)

begin
set @chk=1
select empno
from Emp
where ename = @ename
end

else

begin
insert into emp (ename,sal,deptno)
values ( @ename, @sal, @deptno)
select @@identity
set @chk=0
end

insert_emp @ename='Ali', @sal= 5000,@deptno=2 , @chk output

error
=============
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@chk".

Regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 12:41:27

invocation state ment should be


declare @chkret int
insert_emp @ename='Ali', @sal= 5000,@deptno=2 , @chkret output

select @chkret

skaswani
Starting Member

24 Posts

Posted - 2008-10-22 : 14:46:44

declare @chk int
exec insert_emp @ename='Ali', @sal= 5000,@deptno=2 , @chk output
select @chk

error
=========

Msg 119, Level 15, State 1, Line 2
Must pass parameter number 4 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources