Error when Creating Query

Question

0.00/5 (No votes)

See more:

Hey guys i need your help, i am getting errors when creating this query. What this query does is preety simple. It take 3 parameter firstname, lastname and guid(unique number combination), then it check if that person already exists. If the person does, then output the person's id and if not, insert the person then out the new id. However, it get this error:

Msg 102, Level 15, State 1, Procedure Check_IF_Person_Exist, Line 4
Incorrect syntax near '@intPersonID'.
Msg 137, Level 15, State 1, Procedure Check_IF_Person_Exist, Line 12
Must declare the scalar variable "@intPersonID".
Msg 137, Level 15, State 1, Procedure Check_IF_Person_Exist, Line 19
Must declare the scalar variable "@intPersonID".

SQL

Create Procedure Check_IF_Person_Exist(@FirstName nvarchar(50),@LastName  nvarchar (50),@Guid  int(11))


@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 5:35am

rudolph098

Add a Solution

1 solution

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)

Richard C Bishop · Accepted Answer · 2013-11-05T05:36:00

Solution 1

You need to add this:

DECLARE @intPersonID int

[EDIT]

Create Procedure [dbo].[Check_IF_Person_Exist]

@FirstName nvarchar(50),
@LastName  nvarchar (50),
@Guid  int(11)
 

AS

DECLARE @intPersonID int


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

Posted 5-Nov-13 5:36am

Richard C Bishop

Updated 5-Nov-13 6:00am

v4

Comments

rudolph098 5-Nov-13 11:49am

I did that and i get this error.

Msg 156, Level 15, State 1, Procedure Check_IF_Person_Exist, Line 2
Incorrect syntax near the keyword 'DECLARE'.
Msg 102, Level 15, State 1, Procedure Check_IF_Person_Exist, Line 4
Incorrect syntax near '@intPersonID'.
Msg 137, Level 15, State 1, Procedure Check_IF_Person_Exist, Line 12
Must declare the scalar variable "@intPersonID".
Msg 137, Level 15, State 1, Procedure Check_IF_Person_Exist, Line 19
Must declare the scalar variable "@intPersonID".

Richard C Bishop 5-Nov-13 11:52am

See my updated solution.

rudolph098 5-Nov-13 11:58am

i gives this new error

Msg 102, Level 15, State 1, Procedure Check_IF_Person_Exist, Line 10
Incorrect syntax near 'OUTPUT'.
Msg 137, Level 15, State 1, Procedure Check_IF_Person_Exist, Line 17
Must declare the scalar variable "@intPersonID".
Msg 137, Level 15, State 1, Procedure Check_IF_Person_Exist, Line 24
Must declare the scalar variable "@intPersonID".

rudolph098 5-Nov-13 11:59am

also thanks a lot, i really appreciate your help

Richard C Bishop 5-Nov-13 12:00pm

No problem, just remove the "OUTPUT". It is not needed there.

rudolph098 5-Nov-13 12:05pm

Ok

rudolph098 5-Nov-13 12:09pm

Also i wanted to ask if there is a way to auto generate the date and insert it into the table instead of passing it like i did with firstname,lastname and guid.

Richard C Bishop 5-Nov-13 12:12pm

Ok, if this solution solved your problem, go ahead and accept it as such so it removes it form the unanswered queue. You can use:

DECLARE @CurrentDate as DateTime

SET @CurrentDate = GetDate()

rudolph098 5-Nov-13 12:17pm

Bless you.

Richard C Bishop 5-Nov-13 12:19pm

You are welcome, come back anytime.

CHill60 5-Nov-13 12:46pm

Just spotted your response to my "solution" in my email - I've deleted that rather rushed entry ... that was my 5+ :-)

Richard C Bishop 5-Nov-13 12:49pm

Right on, I sometimes do the same thing. In seeing your solution, it made me go back and see if I happened to misread the question. Thank you for the vote!