SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Incorrect syntax near the keyword 'CASE'
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ryan.gillies
Starting Member

United Kingdom
27 Posts

Posted - 07/27/2012 :  06:23:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 07/27/2012 :  06:28:21  Show Profile  Reply with Quote
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
Go to Top of Page

ryan.gillies
Starting Member

United Kingdom
27 Posts

Posted - 07/27/2012 :  06:37:33  Show Profile  Reply with Quote
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?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 07/27/2012 :  06:51:58  Show Profile  Reply with Quote
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
Go to Top of Page

ryan.gillies
Starting Member

United Kingdom
27 Posts

Posted - 07/27/2012 :  07:03:24  Show Profile  Reply with Quote
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?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 07/27/2012 :  07:15:08  Show Profile  Reply with Quote
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.

Edited by - sunitabeck on 07/27/2012 07:35:42
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000