Value does not apply to variable in SQL Anywhere

Question

when I try to execute this function I get a error. When I execute the command without a function and without using variables it works, therefore I think the value does not apply to my variable, the declaration or the setting of variable does not work. It doesn't matter if I use SELECT or SET to set the variable, I get the same error which literally just says: The command could not be executed.

CREATE FUNCTION ueberpruefe_kfz_status (@kennzeichen CHAR(15))
RETURNS VARCHAR
AS
BEGIN
    DECLARE @ergebnis VARCHAR

    SELECT STATUS
    INTO @ergebnis
    FROM KFZ
    WHERE KENNZEICHEN = @kennzeichen

    RETURN @ergebnis
END

Here are some screenshots of the structure, sample data and the error: https://picload.org/folder/ldpwa.html

Can you share your KFZ table structure and exact error message that you are getting ? — Keyur Panchal, Jun 19 '17 at 12:06
Can you show some sample data and results of the `Select` statement? — SS_DBA, Jun 19 '17 at 12:10
For those curious, the rough translation of the error message in the screen shots is "An error occurred while reading the results of the SQL statement. The results are incorrect or incomplete." — Timothy G., Jun 19 '17 at 12:45
What data type is kennzeichen in kfz? Are you compairing a varchar datatype to a char data type and the padded spaces pf char causing problems? — xQbert, Jun 19 '17 at 13:10

score 1 · Accepted Answer · answered Jun 19 '17 at 13:13

As per your table structure seen in the image, it seems like your column STATUS is of type CHAR. Try to Change your function return type from VARCHAR to CHAR like-

RETURNS CHAR

And type of variable @ergebnis to CHAR like-

DECLARE @ergebnis CHAR

You may specify length along with CHAR, like CHAR(15), which should be greater than or equal to the length of your STATUS column.

Value does not apply to variable in SQL Anywhere

1 Answers1