How can we help you today?
Error in Function
1 Comment
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
Carlos Crespo
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:
But when i try to move this into a function:
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:
What am I doing wrong here?
Thanks,
Carlos Crespo