Where子句和案例陈述 [英] Where clause and Case Statement

问题描述

我是SQL新手。我有这样的存储过程：

Hi,

I am new to SQL. I have a stored proc like this:

CREATE PROCEDURE [dbo].[Developer_Set]
(
	@ID INT,
        @UserID INT,	
	@EffectiveDate DATETIME,
	@EndDate DATETIME = NULL,
	@UserName VARCHAR(255)
)
AS
BEGIN
	
	SET NOCOUNT ON;

	--Mark previous data as old
	UPDATE Developer
	SET DeletionIndicator = 1
	WHERE ID = @ID AND UserID = @UserID
	
	--Insert New Detail
	INSERT INTO dbo.Developer
	        ( ID ,
	          UserID ,
	          EffectiveDate ,
	          EndDate ,	         
	          DeletionIndicator
	        )
	VALUES  ( @ID , -- 
	          @UserID , -- UserID - int
	          @EffectiveDate, -- EffectiveDate - datetime
	          @EndDate, -- EndDate - datetime	          		 
	          CASE WHEN @EndDate IS NULL THEN 0 ELSE 1 END --DeletionIndicator
	        )

	
END

GO

我想要包括这样的Where语句：

I wanted to include a Where Statement like this :

INSERT INTO dbo.Developer
            ( ID ,
              UserID ,
              EffectiveDate ,
              EndDate ,
              DeletionIndicator
            )
    VALUES  ( @ID , --
              @UserID , -- UserID - int
              @EffectiveDate, -- EffectiveDate - datetime
              @EndDate, -- EndDate - datetime
              CASE WHEN @EndDate IS NULL THEN 0 ELSE 1 END --DeletionIndicator
            ) Where ID= @ID

为什么我无法做到这一点？请帮忙。

Why am i not able to do this?Please help.

推荐答案

您不能在insert语句中使用WHERE。其中比较select和update语句中已在数据库中的行。因为您要插入一个NEW行（因此不在数据库中），所以WHERE根本不适用。我不知道如何更清楚地解释它，我很害怕。



我不知道为什么你认为你需要使用它，但是无论为什么，你必须重新思考并弄清楚如何正确地做到这一点......抱歉。

You can't use WHERE in insert statements. Where compares the rows that are already IN the database in select and update statements. Because you are inserting a NEW row (thus one that is NOT in the database), WHERE simply does not apply. I don't know how to explain it any clearer than that, I'm afraid.

I don't know why you think you need to use that, but no matter why, you have to rethink and figure out how to do it correctly... Sorry.

这篇关于Where子句和案例陈述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！