查询无法正常工作 [英] Query not working properly

问题描述

当我将数据库中已经存在的值传递给存储过程时，它不会输出ID.它只是返回一个Null.但是当我插入不存在的值时，它将插入它们并输出id.

When i pass values that already exist in the database to the stored-procedure, it does not output the ID. it just returns a Null. But when i insert values that don''t exist, it inserts them and outputs the id.

 Create Procedure [dbo].[Check_IF_Person_Exist]
 
@FirstName nvarchar(50),
@LastName  nvarchar (50),
@Guid      nvarchar(50),
 
@intPersonID  int OUTPUT
AS
 
 
 

BEGIN
	IF EXISTS( SELECT ID FROM ITTESI_AssetTracker_Person WHERE Guid = @Guid)
		BEGIN
		    -- Assign existing PersonID to output parameter @intPersonID
			SET @intPersonID = @@IDENTITY
		END
	ELSE
		BEGIN
			INSERT INTO ITTESI_AssetTracker_Person VALUES (@Guid,@FirstName,@LastName)
			
			-- Assign newly generated PersonID to output parameter @intPersonID
			SET @intPersonID = @@IDENTITY
		END
END

thanks for your help.

推荐答案

@@ identity仅返回新插入行的标识列的值.
由于您没有在如果存在"语句中插入任何内容，因此它返回null

您必须进行选择并将其分配给@intPersonId

the @@identity returns the identity column''s value of a newly inserted row only.
Since you''re not inserting anything in your ''If Exists'' statement, it is returning null

You must do a select and assign it to @intPersonId

Create Procedure [dbo].[Check_IF_Person_Exist]
 
@FirstName nvarchar(50),
@LastName  nvarchar (50),
@Guid      nvarchar(50),
 
@intPersonID  int OUTPUT
AS
 
 
 

BEGIN

	IF EXISTS( SELECT ID FROM ITTESI_AssetTracker_Person WHERE Guid = @Guid)
		BEGIN
		    -- Assign existing PersonID to output parameter @intPersonID
			SET @intPersonID = ( SELECT [ID] FROM ITTESI_AssetTracker_Person WHERE Guid = @Guid)
		END
	ELSE
		BEGIN
			INSERT INTO ITTESI_AssetTracker_Person VALUES (@Guid,@FirstName,@LastName)
			
			-- Assign newly generated PersonID to output parameter @intPersonID
			SET @intPersonID = @@IDENTITY
		END
END

也感谢stogg

Also thanks stogg

这篇关于查询无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！