无法过滤的SQL存在吗? [英] SQL Where Exists Doesn't Filter?

问题描述

System.Data.SqlClient.SqlException(0x80131904):违反UNIQUE 密钥约束"AK_SeqNo".无法在对象中插入重复键 'dbo.SeqNo'.

System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'AK_SeqNo'. Cannot insert duplicate key in object 'dbo.SeqNo'.

我随机收到上述SQL Server 2005 Express错误.从下面的存储过程中，也许每3周一次.谁能看到原因?

I get the above SQL Server 2005 Express error randomly. Maybe once every 3 weeks from the stored procedure below. Can anyone see why?

CREATE PROCEDURE [dbo].[mspResetSeqNo] @Today DATETIME
AS 
BEGIN
  SET NOCOUNT ON;

  DECLARE @DateSrc DATETIME
  DECLARE @MyGUID UNIQUEIDENTIFIER

  -- Check Input is Valid
  IF @Today IS NULL 
     BEGIN
           RAISERROR (N'@Today cannot be NULL', 10, 1); 
           RETURN 1;
     END

  -- Chop off the time part:
  SET @DateSrc = DATEADD(d, 0, DATEDIFF(d, 0, @Today));

  -- Get Current Location GUID
  SET @MyGUID = dbo.MyGUID();

  -- If this is the first entry for the day then initialise
  INSERT INTO dbo.SeqNo(MyGUID, TheDay, LastNo)
  SELECT @MyGUID, @DateSrc, 0
  WHERE NOT EXISTS ( 
      SELECT 1 FROM dbo.SeqNo AS sn
      WHERE sn.MyGUID = @MyGUID AND sn.TheDay = @DateSrc 
      );

  RETURN(0);
END

AK_SeqNo的约束为:

The constraint for AK_SeqNo is:

ALTER TABLE [dbo].[SeqNo] ADD CONSTRAINT [AK_SeqNo] UNIQUE NONCLUSTERED 
(
[TheDay] ASC,
[MyGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

这两种数据类型的表列为:

The table column for those 2 data types are:

[MyGUID] [dbo].[DForeignKey] NOT NULL,
[TheDay] [datetime] NOT NULL,

用户定义的DForeignKey类型为:

The user defined type DForeignKey is:

CREATE TYPE [dbo].[DForeignKey] FROM [uniqueidentifier] NULL

MyGUID()函数只是检索本地系统ID.每个位置都有不同的ID.

The MyGUID() function is simply retrieving the local system ID. Every location has a different ID.

CREATE FUNCTION [dbo].[MyGUID]()
RETURNS uniqueidentifier
AS
BEGIN
DECLARE @me as uniqueidentifier
SELECT @me = MyGUID FROM Self
RETURN @me
END

推荐答案

问题是并发问题.单实例检查已移动到代码中此存储过程被调用的位置之后.谢谢你的帮助.至少我了解了有关t-sql函数的2件事.

The problem was a concurrency issue. The single instance check was moved to after the point in code where this stored procedure was called. Thanks for your help. At least I learned a thing of 2 more about t-sql functions.

这篇关于无法过滤的SQL存在吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！