[SQLServer] 如果資料表中有Identity欄位，則設Identity_Insert為On再新增資料的預存程序

2011-07-15

38213
0
SQL Server

問題來源：

http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/05ab8545-cf02-4783-90eb-bc30d7f6be3b

一開始在預存程序裡我直接組Set Identity_Insert ON的SQL字串，然後再Execute(SQL字串)

測試執行發現

預存程序雖然執行成功，我再Insert into 資料時會發生

訊息 544，層級 16，狀態 1，行 1
當 IDENTITY_INSERT 設為 OFF 時，無法將外顯值插入資料表 'tb_test' 的識別欄位中。

的錯誤訊息Orz

後來上網找了許久，找到一篇翻譯怪怪的說明文

注意：執行的陳述式行為不正確地組身分插入上

簡單講就是在Execute裡執行Set Identity_Insert的SQL字串會失效

正確做法應該要在Execute裡

Set Identity_Insert ON

/*Insert into 動作*/

Set Identity_Insert OFF

一氣呵成，執行此三行指令才可以，今天算學到一課

附上預存程序

Create Procedure usp_identityinsert
(
@dbName VARCHAR(100),
@schemaName varchar(100),
@tableName VARCHAR(100),
@insertSql Varchar(MAX)
)
AS
BEGIN

 Declare @s varchar(MAX)
 if(OBJECTPROPERTY(OBJECT_ID(@dbName +'.'+@schemaName +'.'+@tableName ,'U'),'TableHasIdentity') > 0)
 Begin
  Set @s = 'SET IDENTITY_INSERT ' + @dbName+'.'+@schemaName+'.'+@tableName+ ' ON;'
   Set @s = @s + @insertSql 
   Set @s = @s + 'SET IDENTITY_INSERT ' + @dbName+'.'+@schemaName+'.'+@tableName+ ' OFF;'
   Execute(@s) 

 End


END
Go;

/*執行範例：*/
Exec usp_identityinsert 'MyDB','dbo','tb_test','Insert into tb_test (uid,title) values (1234,''Shadow'')' /*塞字串要跳脫字元*/

回首頁

注意： 執行的陳述式行為不正確地組身分插入上

注意：執行的陳述式行為不正確地組身分插入上