Stored Procedure - Incorrect syntax near 'RETURN'.

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

Express Edition and Compact Edition (2005)

Stored Procedure - Incorrect syntax near 'RETURN'.

Author

Topic

bordois1984
Starting Member

2 Posts

Posted - 2009-04-04 : 15:50:54

I'm receiving the following error with my stored procedure but I can't see what is going wrong. I'm using SQL Server 2005 Management Studio Express

Can anyone see where I'm going wrong??

Here's the code...



USE [parsimony_op]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_customerCheck]
	@username NChar (6),
	@password NChar (5),
	@first_name NChar (20),
	@last_name NChar (20),
	@address_line1 Nchar (30),
	@address_line2 Nchar (30),
	@postcode Nchar (10),
	@county Nchar (30),
	@contactNumber Nchar (20),
	@emailAddy Nchar (30),
	@dob Nchar (12),
	@gender Nchar (1),
	@incomeBand Nchar (20),
	@homeOwner Nchar (3)
	
AS
If @username = 'remote' AND @password = 'parOp'
BEGIN
IF EXISTS (SELECT * FROM customers WHERE first_name = @first_name AND last_name = @last_name AND postcode = @postcode)
	BEGIN
		RAISERROR ('Customer already exists in the database!',11,1)
	END
ELSE
RAISERROR ('Username and Password required to access the database',11,1)
DECLARE @alterContactNum NChar (20)
	BEGIN
		SET @alterContactNum = '(+44)' + ' ' + SUBSTRING(@contactNumber,2,4) + ' ' + SUBSTRING(@contactNumber,6,20)
		UPDATE customers SET contactNumber = @alterContactNum WHERE @first_name = first_name AND @last_name = last_name
	END
DECLARE @alterPcode NChar (10)
	BEGIN
		SET @alterPcode = UPPER (SUBSTRING(@postcode, 1,10))
		UPDATE customers SET postcode = @alterPcode WHERE @first_name = first_name AND @last_name = last_name
	END	
RETURN

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-04 : 16:39:04

you have mal-formed BEGIN/END blocks.


IF <condition>
BEGIN
   <code>
END
ELSE
BEGIN
   <code>
END

You can omit the BEGIN/END for an IF/ELSE statement but then only one command following the IF and ELSE falls under the conditional part. Even if there is only one line of code in the conditional block I still like to express the BEGIN/END blocks to avoid any confusion.

Be One with the Optimizer
TG

bordois1984
Starting Member

2 Posts

Posted - 2009-04-05 : 07:28:37

I've removed the BEGIN just after the first If statement, however I am now receiving the following error;

Msg 208, Level 16, State 6, Procedure usp_customerCheck, Line 37
Invalid object name 'dbo.usp_customerCheck'.

Apologies for my stupidity, it's my first attempt with stored procedures!

Code is below;


USE [parsimony_op]
GO
/****** Object: StoredProcedure [dbo].[usp_customerCheck]    Script Date 03/28/2009 18:14:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_customerCheck]
	@username NChar (6),
	@password NChar (5),
	@first_name NChar (20),
	@last_name NChar (20),
	@address_line1 Nchar (30),
	@address_line2 Nchar (30),
	@postcode Nchar (10),
	@county Nchar (30),
	@contactNumber Nchar (20),
	@emailAddy Nchar (30),
	@dob Nchar (12),
	@gender Nchar (1),
	@incomeBand Nchar (20),
	@homeOwner Nchar (3)
	
AS
If @username = 'remote' AND @password = 'parOp'

IF EXISTS (SELECT * FROM customers WHERE first_name = @first_name AND last_name = @last_name AND postcode = @postcode)
	BEGIN
		RAISERROR ('Customer already exists in the database!',11,1)
	END
ELSE
RAISERROR ('Username and Password required to access the database',11,1)
DECLARE @alterContactNum NChar (20)
	BEGIN
		SET @alterContactNum = '(+44)' + ' ' + SUBSTRING(@contactNumber,2,4) + ' ' + SUBSTRING(@contactNumber,6,20)
		UPDATE customers SET contactNumber = @alterContactNum WHERE @first_name = first_name AND @last_name = last_name
	END
DECLARE @alterPcode NChar (10)
	BEGIN
		SET @alterPcode = UPPER (SUBSTRING(@postcode, 1,10))
		UPDATE customers SET postcode = @alterPcode WHERE @first_name = first_name AND @last_name = last_name
	END	
RETURN

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-05 : 15:00:03

since your only reference to that name is in the ALTER PROC statement, I'm guessing that you either are in a different database then the procedure resides or the procedure doesn't exist yet. If you are creating one rather than altering an existing one then use CREATE PROC rather than ALTER PROC.

I'm not sure why the error says it is on line 37 though, do you have other code you didn't post (above the alter proc part)?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources