| Author |
 Topic  |
|
|
ryan.gillies
Starting Member
United Kingdom
16 Posts |
 Posted - 07/27/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/27/2012 : 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
United Kingdom
16 Posts |
Posted - 07/27/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/27/2012 : 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
United Kingdom
16 Posts |
Posted - 07/27/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/27/2012 : 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_XIf 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 |
|
|
| |
Topic |
|
Incorrect syntax near the keyword 'CASE'
