Function creation error

Could anyone let me know quickly, Whats wrong in the below function creation..

CREATE FUNCTION DBO.Generate
(
@val DATETIME
)
RETURNS INT
BEGIN
CASE @val
WHEN NULL THEN
-- MESSAGE 'Generate;
RETURN (0);
ELSE
RETURN (Generate(CONVERT(CHAR, @val)));
END
END
GO

While creation of above, getting below error.

use IF ... ELSE instead of CASE ... WHEN

CASE ... WHEN only valid within a SELECT statement




KH

check this:

CREATE FUNCTION DBO.GenerateControlValueDate
(
@val DATETIME
)
RETURNS INT
BEGIN
declare @val1 int
SELECT @val1=CASE @val
WHEN NULL THEN
-- MESSAGE 'GenerateControlValueDate = NULL';
0
ELSE dbo.GenerateControlValueChar(CONVERT(CHAR, @val))
END
return @val1
END
GO

Thanks ranjit for immediate support.
Now its working. Same way followed with the below query, but i am getting error for this. If you dont mind, could you guid me on this.

Thanks in Advance.
~ HNS ~

Sybase SQL?

Anyway...

CASE WHEN ... THEN ... ELSE ... END

You must always close off a CASE construct with an END.

I need the above query to run on MSSQL2000. Please Where i am wrong..

You still seem to be confusing IF and CASE.

IF <condition>
BEGIN
SET @var1 = 'blabla'
SET @var2 = 'gligli'
END
ELSE
BEGIN
SET @var1 = 'gligli'
SET @var2 = 'blabla'
END

This is the same as:

SET @var1 = CASE WHEN <condition> THEN 'blabla' ELSE 'gligli' END
SET @var2 = CASE WHEN <condition> THEN 'gligli' ELSE 'blabla' END

***

The whole CASE expression returns a single value, which you can assign to a single variable. The assignment is always executed, the return value is set conditionally.

The IF constuct is control-of-flow syntax, so the assignments within the IF syntax are executed conditionally.

If you want this function to be in sql 2000, then you should go for what khtan has suggested.
Replace select case with
IF ELSE like:
IF @VAL IS NULL
BEGIN
-- MESSAGE 'GenerateControlValueChar = NULLE'
@ret = 0
END
ELSE
BEGIN
@i = 1
@taille = LENGTH(@val)
@ret = 0

WHILE @i <= @taille
BEGIN
SET @ret = @ret + ASCII(SUBSTRING(@val, @i, 1))
SET @i = @i + 1
END
-- MESSAGE 'GenerateControlValueChar (' + @val + ' = ' , CONVERT(CHAR, @ret), ')';
END

ranitjain,

There's just too many assingments without the SET keyword in that script ... the fault is with the original poster entirely.

Original poster needs to sort out the control-of-flow he needs, then see if he still needs a CASE.

... +1 on Frank's comments.

I agree, adriaan.
I think it will be better if the original posterr posts his requirement and clarify what his function is suppose to do.
I think he is checking, whetherr the value is null or not and if null calling another function to convert it to varchar.
This can be achived inside procedure itself without having to do this function call.

So better post ur requirement.

Hi
Sorry for confussion with wrong posting, Here is what is the original one,
I would like to convert it into MSSQL2000 Compatible. Please help me with one, so that i can proceed with others.

Orginal SYBASE SQL Anywhere Query:
------------------------------------
CREATE FUNCTION DBO(
@val VARCHAR(8000)
)
RETURNS INT
~ HNS ~

I think there is no need to check for NULL.
Here is altered version:

ALTER FUNCTION DBO.GenerateControlValueChar (@val VARCHAR(8000))RETURNS INT
AS
BEGIN
DECLARE @ret INT
DECLARE @i INT
DECLARE @taille INT
IF (@val IS NOT NULL)
BEGIN
SET @i = 1;
SET @taille = LEN(@val);
SET @ret = 0;
WHILE @i <= @taille
BEGIN
SET @ret = @ret + ASCII(SUBSTRING(@val, @i, 1));
SET @i = @i + 1;
END
END
RETURN (@ret);
END

Hi Frank,
Function definition explicitly asks for input, so how can be a call made to this function
without any input.

HNS,

You really need to study the control-of-flow elements of Microsoft T-SQL more carefully. The Sybase SQL syntax is definitely different!

Ranjit,

How can i call function from OSQL command prompt. When i calling using
1> call <function_name>
2> go

Getting error as :
Thanks
~ HNS ~

Hi Ranjit,

How can i call function from OSQL command prompt. When i calling using
1> call <function_name>
2> go

Getting error as :

1> call GenerateControlValueChar
2> go

Please let me know where am i wrong.

Thanks
~ HNS ~

Ranjit / Frank

Thanks for your response. Function modified by you is working fine. After that fuction, there is another function is making call to first one. Here is the second function. Buts its giving error. COrrect me on this.

1. CREATE FUNCTION DBO.Generate (@val VARCHAR(8000)) - Its corrected.

2. looks below function is making call to above one(DBO.Generate).
CREATE FUNCTION DBA.Date
(
@val TIMESTAMP
)
~ HNS ~

Hi Frank,

Thanks a lot for continuous support. Now i got stuck on this ...

CREATE FUNCTION DBA.GenerateControlValue
(
@sTable CHAR(255),
@id INT
)
RETURNS INT
BEGIN
DECLARE @query VARCHAR(32767);
DECLARE @crc INT;
SET @query = 'Select @crc = GenerateControlValueChar('''+UCASE(@sTable)+''')';
boucleCurseur:
FOR crcFor AS curRecord CURSOR
FOR
select cname AS fieldName, coltype AS fieldType
from sys.syscolumns
where tname = @sTable
DO
IF fieldname <> 'ComponentId' THEN
CASE fieldType
WHEN 'integer' THEN
SET @query = @query + ' + GenerateControlValueInt(' + fieldName + ')';
WHEN 'smallint' THEN
SET @query = @query + ' + GenerateControlValueInt(' + fieldName + ')';
ELSE
MESSAGE @sTable + '.' + fieldName + ' -> ' + fieldType + ' inconnu';
END CASE;
END IF;
END FOR boucleCurseur;

I am not getting whats is this statement in the above function(SybaseSQL Anywhere). If you could help me the MSSQL2000 compatible code of this, it will be great help.

boucleCurseur:
FOR crcFor AS curRecord CURSOR

END FOR boucleCurseur;
SET @query = @query + ' FROM DBA.' + @sTable + ' WHERE ' + @sTable +'.ComponentId = ' + CONVERT(CHAR, @id);
EXECUTE IMMEDIATE @query;
RETURN (@crc);
END
GO

Awaiting for your response...

Thanks
~ HNS ~