SQL Server Performance

Function creation error

Discussion in 'General DBA Questions' started by homnath_sharma, Oct 18, 2006.

  1. homnath_sharma New Member

    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.
  2. khtan New Member

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

    CASE ... WHEN only valid within a SELECT statement




    KH
  3. ranjitjain New Member

    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
  4. homnath_sharma New Member

    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 ~

  5. Adriaan New Member

    Sybase SQL?

    Anyway...

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

    You must always close off a CASE construct with an END.
  6. homnath_sharma New Member

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



  7. Adriaan New Member

    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.
  8. ranjitjain New Member

    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
  9. FrankKalis Moderator

  10. Adriaan New Member

    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.
  11. ranjitjain New Member

    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.
  12. homnath_sharma New Member

    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 ~
  13. FrankKalis Moderator


    CREATE FUNCTION DBO.GenerateControlValueChar (@val VARCHAR(8000))
    RETURNS INT
    AS
    BEGIN
    DECLARE @ret INT
    DECLARE @i INT
    DECLARE @taille INT

    IF (@val IS NULL)
    BEGIN
    RETURN NULL
    -- MESSAGE 'GenerateControlValueChar = NULLE';
    END
    ELSE
    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

    RETURN (@ret);
    END
    GO


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  14. ranjitjain New Member

    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
  15. FrankKalis Moderator

    Hm, and what to return when there is no input?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  16. ranjitjain New Member

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

    Oops, missed that one. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  18. Adriaan New Member

    HNS,

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

    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 ~
  20. homnath_sharma New Member

    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 ~
  21. homnath_sharma New Member

    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 ~
  22. FrankKalis Moderator

    This is how you nest UDF's in SQL Server:


    USE tempDB
    CREATE FUNCTION dbo.t1 (@a INT)
    RETURNS INT
    AS
    BEGIN
    RETURN @a
    END
    GO
    CREATE FUNCTION dbo.t2()
    RETURNs INT
    AS
    BEGIN
    RETURN(SELECT dbo.t1(2) * 2)
    END
    GO

    SELECT dbo.t2()
    DROP FUNCTION dbo.t1, dbo.t2

    -----------
    4

    (1 row(s) affected)

    So, in your case a


    RETURN (SELECT GenerateControlValueChar(CONVERT(CHAR, @val)));

    should do.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  23. homnath_sharma New Member

    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 ~
  24. FrankKalis Moderator

    Sorry, but you're on a completely wrong track.
    It seems as if you want to dynamically create a query inside a UDF. This is syntactically not allowed in SQL Server. You have to find another way.

    Btw, a cursor, dynamic SQL and a UDF (to a certain degree) are considered things to stay away from in SQL Server.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  25. satya Moderator

    Is it not a best time to go thru SQL Server books online for all the bits and pieces [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>

Share This Page