Query not working properly

Question

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 5-Nov-13 7:37am

rudolph098

Add a Solution

Comments

RedDk 5-Nov-13 13:42pm

double Repost

2 solutions

Solution 1

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

Posted 5-Nov-13 7:45am

strogg

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?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

rudolph098 · Accepted Answer · 2013-11-05T08:03:00

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