Incorrect syntax near the keyword 'CASE'

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

General SQL Server Forums

New to SQL Server Programming

Incorrect syntax near the keyword 'CASE'

Author

Topic

ryan.gillies
Starting Member

27 Posts

Posted - 2012-07-27 : 06:23:14

Hi all

I'm making my first steps into the world of SQL Server and TSQL having spent a long time working on Access databases, so its new and exciting times for me!

I've been attempting to duplicate a VBA function in TSQL, but something about my case statement isn't standing up properly - I continue to receive a Incorrect syntax near the keyword 'CASE' error, and again for SELECT at the beginning of my SELECT statement.

Can anyone point me in the direction of where I'm going wrong?

CREATE FUNCTION ZZ(@CompDetails varchar(MAX))
	
	RETURNS varchar(16)
	
	AS
	BEGIN
	
	DECLARE @Return varchar(16)
	DECLARE @zz1 int
	DECLARE @zz2 int
	
	CASE
	WHEN @CompDetails NOT LIKE 'ZZ[1-9A-Ga-g]ZZ%'
	THEN @Return = ''
	ELSE
	@zz1 = CHARINDEX('ZZ', @CompDetails) + 2
	@zz2 = CHARINDEX('ZZ', @CompDetails, @zz1)
	@Return = SUBSTRING(@CompDetails, zz1, zz2 - zz1)
	END
	
	RETURN @Return
	
END

SELECT
	Contact_Part_X.Contact_Id,
	Contact_Part_X.Contact_Part_Id,
...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-27 : 06:28:21

Didn't 100% follow the logic you are implementing, but something like this, from what I understood.

CREATE FUNCTION ZZ(@CompDetails varchar(MAX))
	
	RETURNS varchar(16)
	
	AS
	BEGIN
	
	DECLARE @Return varchar(16)
	DECLARE @zz1 int
	DECLARE @zz2 INT
	
	IF (@CompDetails NOT LIKE 'ZZ[1-9A-Ga-g]ZZ%')
	BEGIN
		SET  @Return = ''
	END 
	ELSE
		BEGIN
				SET @zz1 = CHARINDEX('ZZ', @CompDetails) + 2
				SET @zz2 = CHARINDEX('ZZ', @CompDetails, @zz1)
				SET @Return = SUBSTRING(@CompDetails, @zz1, @zz2 - @zz1)
		END
	

	
	RETURN @Return
	
END

ryan.gillies
Starting Member

27 Posts

Posted - 2012-07-27 : 06:37:33

Hi sunitabeck

Thanks for the rapid response, the logic is sound and that is no longer throwing up an error in my query, so thank you for that. Am I not able to use CASE statements inside of functions?

It hasn't resolved the second issue however of Incorrect syntax near the keyword 'SELECT'. I want to call the above function to populate a field inside of my select query, the beginning of the SELECT query goes like this:

SELECT
	Contact_Part_X.Contact_Id,
	Contact_Part_X.Contact_Part_Id,
	dbo.ZZ(Contact_Part_X.T3) AS [ZZ],
...

Am I missing something?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-27 : 06:51:58

You can use CASE inside functions. But, CASE is not a statement, it is an expression. So you would use it in a select statement, for example:

DECLARE @widgetCount INT;
SET @widgetCount = 50;

SELECT 
	CASE 
		WHEN @widgetCount > 10 THEN 'I have more than 10 widgets'
		ELSE 'I have only 10 or fewer widgets'
	END

There are various use cases here: http://msdn.microsoft.com/en-us/library/ms181765.aspx

ryan.gillies
Starting Member

27 Posts

Posted - 2012-07-27 : 07:03:24

Ok I see, that resolves that particular query of mine.

Do you have any thoughts on the other problem? Is there a way for me to populate a field in a select query using the above function?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-27 : 07:15:08

First remove the select statement and run only the script for creating the function. You need to do that only once. The function is created, and it is there for your use. Now, remove the code for the function creation and write only the select statement. It should work correctly. For example:

SELECT
	Contact_Part_X.Contact_Id,
	Contact_Part_X.Contact_Part_Id,
	dbo.ZZ(Contact_Part_X.T3) AS [ZZ]
FROM
        Contact_Part_X

If your function is implementing only the logic that you have in your example, it may be simpler to simply embed the logic in your select statement. There is an overhead to calling a function, which can then be avoided.

Edit: I assume T3 is a column in the Contact_Part_X table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources