Function Call from a Strore Procedure

Mar 23, 2011 at 5:42 PM

Hi Igloobone:

Today, i was trying calling a function from a Strore Procedure, but i got an error and it's seems to be a problem.

The function:

DROP FUNCTION IF EXISTS FUNC
CREATE FUNCTION FUNC(@ID INT)
RETURNS VARCHAR(16)
BEGIN
    DECLARE @res VARCHAR(16);
    IF @ID=0 THEN
        SET @res='Cero';
    ELSE
        SET @res='No Cero';
    END IF;
    RETURN @res;
END

The Store Procedure:

DROP PROCEDURE IF EXISTS PROC
CREATE PROCEDURE PROC()
BEGIN
    DECLARE TABLE @VAL (I INT);
    INSERT INTO @VAL (I) VALUES ((1),(0),(0),(1),(0));
   
    SELECT I,FUNC(I) AS T FROM @VAL;
END

The following error appears íf i use @VAL or a real table.

Error -458 (S1000): System.NullReferenceException: Object reference not set to an instance of an object.
   at EffiProz.Core.Statements.StatementQuery.CollectTableNamesForRead(OrderedHashSet`1 set)
   at EffiProz.Core.Statements.StatementDMQL.SetDatabseObjects(Session session, CompileContext compileContext)
   at EffiProz.Core.Parsing.ParserDQL.CompileCursorSpecification(Int32 props, Boolean isRoutine, RangeVariable[] outerRanges)
   at EffiProz.Core.Parsing.ParserRoutine.CompileSelectStatement(RangeVariable[] rangeVars, Routine routine)
   at EffiProz.Core.Parsing.ParserRoutine.CompileSQLProcedureStatementOrNull(Routine routine, StatementCompound context)
   at EffiProz.Core.Parsing.ParserRoutine.CompileSqlProcedureStatementList(Routine routine, StatementCompound context)
   at EffiProz.Core.Parsing.ParserRoutine.CompileCompoundStatement(Routine routine, StatementCompound context, QName label)
   at EffiProz.Core.Parsing.ParserRoutine.CompileSQLProcedureStatementOrNull(Routine routine, StatementCompound context)
   at EffiProz.Core.Parsing.ParserRoutine.ReadRoutineBody(Routine routine)
   at EffiProz.Core.Parsing.ParserRoutine.CompileCreateProcedureOrFunction()
   at EffiProz.Core.Parsing.ParserDDL.CompileCreate()
   at EffiProz.Core.Parsing.ParserCommand.CompilePart(Int32 props)
   at EffiProz.Core.Parsing.ParserCommand.CompileStatements(String sql, Result cmd)
   at EffiProz.Core.Engine.Session.ExecuteDirectStatement(Result cmd) Object reference not set to an instance of an object.
Failed to execute SQL command.

Thanks in advance.