如何在不设置IDENTITY_Insert的情况下执行插入存储过程？ [英] How to execute an insert stored procedure without setting IDENTITY_Insert ON?

问题描述

我在这里创建了一个用于创建表的脚本：

I have a script for table creation here as :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PluginSettingXrefValue](
	[PluginSettingXrefValueID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_PluginSettingXrefValue_PluginSettingXrefValueID]  DEFAULT (newsequentialid()),
	[PluginSettingInstanceID] [int] NOT NULL,
	[PluginSettingID] [uniqueidentifier]  NOT NULL,
	[PluginSettingValue] [nvarchar](200) NULL,
	[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_PluginSettingXrefValue_ris_CreatedDate]  DEFAULT (getdate()),
	[CreatedBy] [nvarchar](200) NULL,
	[UpdatedDate] [datetime] NULL,
	[UpdatedBy] [nvarchar](200) NULL,
	[PluginID] [int] IDENTITY(1,1) NOT NULL
 CONSTRAINT [PK_PluginSettingXrefValue] PRIMARY KEY CLUSTERED 
(
	[PluginSettingXrefValueID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

以及在表中插入值的存储过程：

And a stored procedure to insert Values in the table :

/****** Object:  StoredProcedure [dbo].[usp_CopyPluginSettingToXrefValue]    Script Date: 07/22/2014 11:32:45 ******/

Create Procedure [dbo].[usp_CopyPluginSettingToXrefValue]
(
@InstanceId int
)
As
Begin
declare @result as bit
IF  EXISTS (SELECT * FROM PlugInSettingValues)
BEGIN
IF  NOT EXISTS (SELECT * FROM PluginSettingXrefValue where PluginSettingInstanceId=@InstanceId)
BEGIN
     insert into PluginSettingXrefValue(PluginSettingInstanceID,PluginSettingID,
PluginSettingValue,CreatedDate,CreatedBy,UpdatedDate,UpdatedBy,PluginID) 
select @InstanceId,PlugInSettingID,PlugInSettingValue,GETDATE(),CreatedBy,null,null,PlugInID from PlugInSettingValues
set @result=1
END
END
End
RETURN @result
GO

但是我在执行存储过程时出现错误和一个简单的插入查询，即

But i am getting an error while executing the stored procedure only and a simple insert query i.e.

insert into PluginSettingXref values('2365700a-4c0e-e411-a3e4-5c260a0854c3',1,'24065243-d2ed-40f4-a275-021dc14cf746','false','2014-07-18 12:50:49.337',NULL,NULL,NULL)

给出错误：

is giving an error :

An explicit value for the identity column in table 'PluginSettingXrefValue' can only be specified when a column list is used and IDENTITY_INSERT is ON.

这里出了什么问题？

设置Identity_Insert ON不是一个选项，因为它可能会为注射攻击留出空间。

推荐答案

你必须从insert语句中排除[PluginID]。

You must exclude [PluginID] from the insert statement.

insert into PluginSettingXref([PluginSettingXrefValueID],[PluginSettingInstanceID],[PluginSettingID],[PluginSettingValue],[CreatedDate],[CreatedBy],[UpdatedDate],[UpdatedBy])
values('2365700a-4c0e-e411-a3e4-5c260a0854c3',1,'24065243-d2ed-40f4-a275-021dc14cf746','false','2014-07-18 12:50:49.337',NULL,NULL,NULL)

您需要在insert语句中指定列。你有什么认为你想把'2365700a-4c0e-e411-a3e4-5c260a0854c3'放入 PluginSettingXrefValueID 哪个是非法的。因此消息。

You need to specify your columns in your insert statement. With what you have it thinks you want to put '2365700a-4c0e-e411-a3e4-5c260a0854c3' into PluginSettingXrefValueID which is illegal. Hence the message.

这篇关于如何在不设置IDENTITY_Insert的情况下执行插入存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！