Syntax Stumper! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Syntax Stumper!

Author

Topic

tracyterrell
Starting Member

2 Posts

Posted - 2010-04-13 : 15:47:21

I'm trying to create a simple "Add" sproc, and I get the syntax error:

Msg 102, Level 15, State 1, Procedure rceAddPerson, Line 13
Incorrect syntax near '='.

I've double checked syntax and correct naming of columns. Can someone help me?!
Thanks!
Tracy

USE [A3400080]
GO
/****** Object: StoredProcedure [dbo].[rceAddPerson] Script Date: 04/13/2010 12:40:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE dbo.rceAddPerson

@UserID nvarchar(100),
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Salutation nchar(10)

AS

DECLARE @PersonID int

IF EXISTS (SELECT @PersonID = PersonID
FROM dbo.rcePerson
WHERE (FirstName = @FirstName AND LastName = @LastName)
ELSE
BEGIN
BEGIN
INSERT INTO dbo.rcePerson (
FirstName,
LastName,
Salutation,
createdBy,
createdOn
)
VALUES (
@FirstName,
@LastName,
@Salutation,
@UserID,
CURRENT_TIMESTAMP
)

SELECT @PersonID = SCOPE_IDENTITY()
END
END

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-04-13 : 15:53:40

Indenting helps:



ALTER PROCEDURE dbo.rceAddPerson

@UserID nvarchar(100),
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Salutation nchar(10)

AS

DECLARE @PersonID int

IF EXISTS (SELECT @PersonID = PersonID FROM dbo.rcePerson WHERE (FirstName = @FirstName AND LastName = @LastName) ) --< missing closing paranthesis
-- code missing for expression evaluating to TRUE part
ELSE 
BEGIN
	BEGIN
		INSERT INTO dbo.rcePerson (
		FirstName, 
		LastName, 
		Salutation,
		createdBy,
		createdOn
		)
		VALUES (
		@FirstName, 
		@LastName, 
		@Salutation,
		@UserID,
		CURRENT_TIMESTAMP
		)

		SELECT @PersonID = SCOPE_IDENTITY()
	END
END

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-04-13 : 15:55:37

As you can see, you are missing the code for expresison = TRUE part.
simpler version would be:



ALTER PROCEDURE dbo.rceAddPerson

@UserID nvarchar(100),
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Salutation nchar(10)

AS

DECLARE @PersonID int

IF NOT EXISTS (SELECT * FROM dbo.rcePerson WHERE FirstName = @FirstName AND LastName = @LastName ) 
BEGIN
		INSERT INTO dbo.rcePerson (
		FirstName, 
		LastName, 
		Salutation,
		createdBy,
		createdOn
		)
		VALUES (
		@FirstName, 
		@LastName, 
		@Salutation,
		@UserID,
		CURRENT_TIMESTAMP
		)

		SELECT @PersonID = SCOPE_IDENTITY()
END

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tracyterrell
Starting Member

2 Posts

Posted - 2010-04-13 : 16:00:35

dinakar - THANK YOU!

so simple, yet I poured over it for an hour...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources