Click here to Skip to main content
15,922,007 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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.

SQL
 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.
Posted
Comments
RedDk 5-Nov-13 13:42pm    
double Repost

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
 
Share this answer
 
Comments
rudolph098 5-Nov-13 13:59pm    
IF EXISTS( SELECT ID FROM ITTESI_AssetTracker_Person WHERE Guid = @Guid)
BEGIN
-- Assign existing PersonID to output parameter @intPersonID
SET @intPersonID = ID
END
ELSE

like this?
SQL
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


Also thanks stogg
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900