Error in Function : VistaDB Support Center

How can we help you today?

Start a new topic

Error in Function

Carlos Crespo

started a topic over 8 years ago

Hi,

I am getting a error when I try to move a script to a function if I declared a table inside the function.

This script works as expected:

DECLARE @T1 AS TABLE (SALESPERSON NVARCHAR(10),  SALESMONTH SMALLINT NOT NULL DEFAULT (0), SALESVALUE DECIMAL NOT NULL DEFAULT 0)
DECLARE @T2 AS TABLE (SALESPERSON NVARCHAR(10), TOTALSALES DECIMAL NOT NULL DEFAULT 0)
INSERT INTO @T1 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 1, 10)
INSERT INTO @T1 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 1, 20)
INSERT INTO @T1 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 2, 30)
INSERT INTO @T1 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 3, 40)
INSERT INTO @T1 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('MARY', 1, 50)
INSERT INTO @T2 (SALESPERSON, TOTALSALES) (SELECT SALESPERSON, SUM(SALESVALUE) FROM @T1 GROUP BY SALESPERSON)
SELECT SALESPERSON, TOTALSALES FROM @T2

But when i try to move this into a function:

CREATE FUNCTION [FN_SalesBySalesPerson]
()
RETURNS  @T2 TABLE (SALESPERSON NVARCHAR(10), TOTALSALES DECIMAL NOT NULL DEFAULT 0)
AS
BEGIN
DECLARE @T1 AS TABLE (SALESPERSON NVARCHAR(10),  SALESMONTH SMALLINT NOT NULL DEFAULT (0), SALESVALUE DECIMAL NOT NULL DEFAULT 0)
INSERT INTO @T1 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 1, 10)
INSERT INTO @T1 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 1, 20)
INSERT INTO @T1 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 2, 30)
INSERT INTO @T1 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 3, 40)
INSERT INTO @T1 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('MARY', 1, 50)
INSERT INTO @T2 (SALESPERSON, TOTALSALES) (SELECT SALESPERSON, SUM(SALESVALUE) FROM @T1 GROUP BY SALESPERSON)
RETURN
END

I got a "Um item com a mesma chave já foi adicionado" which can be translated to "A item with the same key has already been added".

Full error:

________________________
Member: Message
Error 509 (Provider v. 5.0.6.1357): Invalid or incomplete statement: INSERT
Line #: 6; Column #: 1
________________________
Member: Source
VistaDB.5.NET40
________________________
Member: Program Location
Program Location:
em VistaDB.Engine.SQL.BatchStatement.CheckBatchExceptions()
em VistaDB.Engine.SQL.BatchStatement.NextResult(VistaDBPipe pipe)
em VistaDB.Provider.VistaDBDataReader.GoNextResult()
em VistaDB.Provider.VistaDBDataReader..ctor(IQueryStatement statement, VistaDBConnection connection, CommandBehavior commandBehavior)
em VistaDB.Provider.VistaDBCommand.ExecuteReader(CommandBehavior behavior)
em DataBuilder.SQLForm.ExecuteQuery(String sqlText)
________________________
Member: Message
Um item com a mesma chave já foi adicionado.
________________________
Member: Source
mscorlib
________________________
Member: Program Location
Program Location:
em System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
em VistaDB.Engine.SQL.BatchStatement.DoRegisterTemporaryTableName(String paramName, CreateTableStatement createTableStatement)
em VistaDB.Engine.SQL.StoredFunctionBody.DoGetTemporaryTableName(String paramName)
em VistaDB.Engine.SQL.SQLParser.TreatTemporaryTableName(String tableName, Statement statement)
em VistaDB.Engine.SQL.InsertStatement.OnParse(LocalSQLConnection connection, SQLParser parser)
em VistaDB.Engine.SQL.Statement.Parse(LocalSQLConnection connection, SQLParser parser)

What am I doing wrong here?

Thanks,

Carlos Crespo

1 Comment

Rob Parker

said over 8 years ago

The error is actually due to the @T1 declaration. There is a flaw in the handling of table variables which breaks them within table-valued functions (but table variables do seem to work okay in scalar-valued functions). We'll increase the priority of trying to fix this.

In the mean time, temp tables do seem to work okay (although it's a less-than-ideal approach):

CREATE FUNCTION [FN_SalesBySalesPerson2]
()
RETURNS @return TABLE (SALESPERSON NVARCHAR(10), TOTALSALES DECIMAL NOT NULL DEFAULT 0)
AS
BEGIN
CREATE TABLE #F_SBSP2 (SALESPERSON NVARCHAR(10), SALESMONTH SMALLINT NOT NULL DEFAULT (0), SALESVALUE DECIMAL NOT NULL DEFAULT 0)
INSERT INTO #F_SBSP2 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 1, 10)
INSERT INTO #F_SBSP2 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 1, 20)
INSERT INTO #F_SBSP2 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 2, 30)
INSERT INTO #F_SBSP2 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 3, 40)
INSERT INTO #F_SBSP2 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('MARY', 1, 50)
INSERT INTO @return (SALESPERSON, TOTALSALES) (SELECT SALESPERSON, SUM(SALESVALUE) FROM #F_SBSP2 GROUP BY SALESPERSON)
DROP TABLE #F_SBSP2
RETURN
END

This seems to produce the expected results for SELECT * FROM FN_SalesBySalesPerson2(); Note that it's best to ensure the temp table name will be unique to that function and ensure that it is cleaned up. Obviously any recursion with this function would be problematic with this approach.

Depending on your needs you might also consider a scalar-valued function instead:

CREATE FUNCTION [FN_SalesBySalesPerson3]
(@salesperson NVARCHAR(10) = NULL)
RETURNS DECIMAL
AS
BEGIN
DECLARE @T1 AS TABLE (SALESPERSON NVARCHAR(10), SALESMONTH SMALLINT NOT NULL DEFAULT (0), SALESVALUE DECIMAL NOT NULL DEFAULT 0)
INSERT INTO @T1 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 1, 10)
INSERT INTO @T1 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 1, 20)
INSERT INTO @T1 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 2, 30)
INSERT INTO @T1 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('JOHN', 3, 40)
INSERT INTO @T1 (SALESPERSON, SALESMONTH, SALESVALUE ) VALUES ('MARY', 1, 50)
DECLARE @totalsales DECIMAL
IF @salesperson IS NULL
SELECT @totalsales = SUM(SALESVALUE) FROM @T1
ELSE
SELECT @totalsales = SUM(SALESVALUE) FROM @T1 WHERE SALESPERSON = @salesperson
RETURN @totalsales
END