IDENTITY_INSERT in Stored Procedure

Hi,
I want to insert a record into a table which is having an identity column.
when i am explicitly setting the IDENTITY_INSERT ON, i am able to insert, but when i use the same in a stored procedure, it is not working. The below line is not working in the SP

set @sql='SET IDENTITY_INSERT '+@TableName+' ON'
EXEC (@sql)
here table name i am passing as parameter.
Can anybody please help me.

Thanks
Dhanraj
Dhanraj_kAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
as said, you will need to run all in one:

set @IdentitySql=' SET IDENTITY_INSERT '+@TableName+' ON '  
set @IdentitySql2=' SET IDENTITY_INSERT '+@TableName+' OFF '  

EXEC (@IdentitySql  +  @sInsertQuery + @IdentitySql2  )

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7zas.asp

the issue is that if you put only the SET IDENTITY_INSERT  ... statement into th EXEC(), the effect of the SET will be lost when the EXEC is completed...

now, why do you want to use the table name as parameter? can you explain a bit more about your implementation requirements?
0
 
rw3adminCommented:
you have to do your complete insert statement along with IDENTITY_INSERT on and off in one dynamic query

0
 
Dhanraj_kAuthor Commented:
hi angelIII,

my stored procedure is used to insert deleted records to different tables. It will accept the insert query, table name and a boolean IsIdentity as parameters.

if IsIdentity is true, then it will execute the following.
 set @IdentitySql='SET IDENTITY_INSERT '+@TableName+' ON'  
         EXEC (@IdentitySql)
         EXEC (@sInsertQuery)

Regards
Dhanraj
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.