SQL Server table function doesn't return values

Question

I am writing this table function

CREATE FUNCTION udf_RealEstateAgentSales
    (@FirstName NVARCHAR, @SecondName NVARCHAR, @LastName NVARCHAR)
RETURNS @salesAmoutAndAgent TABLE (FirstName NVARCHAR(25),
                                   SecondName NVARCHAR(25),
                                   LastName NVARCHAR(25),
                                   sales INT)
AS
BEGIN
    DECLARE @sales INT;
    SET @sales = COUNT(*);

    INSERT @salesAmoutAndAgent
        SELECT @FirstName, @SecondName, @LastName, @sales
        FROM Purchases AS P
        INNER JOIN Employees AS E ON P.DealMadeByEmployeeID = E.EmployeeID
        WHERE 
            @FirstName = FirstName 
            AND @SecondName = SecondName 
            AND @LastName = LastName

    RETURN
END

and I want it to return the firstName, secondName, lastName and sales made depending on his first, second and last name...

But when I call it

SELECT * 
FROM dbo.RealEstatudf_eAgentSales('somename', 'somename', 'somename');

it returns an empty table... what is my mistake?

Bad habits to kick : declaring VARCHAR without (length) - you should always provide a length for any varchar variables and parameters that you use. If you use only NVARCHAR - then you get a string of EXACTLY 1 character length which is usually not what you want..... provide a reasonable length for your parameters! — marc_s
– marc_s, Commented Jan 2, 2017 at 21:54
@marc_s . . . That would explain the problem. You should provide a fuller explanation as an answer. — Gordon Linoff
– Gordon Linoff, Commented Jan 2, 2017 at 21:55
It's hard to tell without data. A lot of things can make the query return the data. There could be one of the conditions not true or the inner join makes the result empty. — Sefe
– Sefe, Commented Jan 2, 2017 at 21:58

Community · Accepted Answer · 2020-07-29 18:26:20Z

2

First of all: Bad habits to kick : declaring VARCHAR without (length) - you should always provide a length for any (n)varchar variables and parameters that you use. If you use NVARCHAR - then you get a string of EXACTLY 1 character length which is usually not what you want..... provide a reasonable length for your parameters! Otherwise, your value somename passed in the call is being truncated to just s .....

Next: you're selecting but you're using the parameter names (with the leading @) - not the columns names - that won't work.

And thirdly: your WHERE clause is all wrong - you need to write it

WHERE (column name) = @(parameter name)

Try this code:

CREATE FUNCTION udf_RealEstateAgentSales
    (@FirstName NVARCHAR(25),
     @SecondName NVARCHAR(25), 
     @LastName NVARCHAR(25) )
RETURNS @salesAmoutAndAgent TABLE (FirstName NVARCHAR(25),
                                   SecondName NVARCHAR(25),
                                   LastName NVARCHAR(25),
                                   sales INT)
AS
BEGIN
    DECLARE @sales INT;
    SET @sales = COUNT(*);

    INSERT @salesAmoutAndAgent
        SELECT 
            FirstName, SecondName, LastName, sales
        FROM 
            Purchases AS P
        INNER JOIN 
            Employees AS E ON P.DealMadeByEmployeeID = E.EmployeeID
        WHERE 
            FirstName = @FirstName 
            AND SecondName = @SecondName 
            AND LastName = @LastName

    RETURN
END

Also: that line

SET @sales = COUNT(*);

really doesn't make any sense at all - you need to rethink (and re-code) this to something more meaningful.... You're counting from what table? You definitely need AT LEAST a FROM (table name) , and most likely also some reasonable WHERE clause......

edited Jul 29, 2020 at 18:26

CommunityBot

11 silver badge

answered Jan 2, 2017 at 21:58

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MitkoZ Over a year ago

it kindda worked thanks... but I want every agent to be in a the same column... now i get somename somename somename 1 somename somename somename 1 .. 2 times.. PS you missed a @ before sales in the select clause

marc_s Over a year ago

@MitkoZ: please provide useful input data and expected output in that case!

MitkoZ Over a year ago

I want for example to get an output John Oliver Stone 2 (the number is the number of times the where statement meets this condition)

MitkoZ Over a year ago

i want the same names to appear in one column properly counted... for this example 2

marc_s · Accepted Answer · 2017-01-03 18:09:04Z

0

I found some errors in your code that you can check below

REMARKS

In your description you placed the call to the function with the name RealEstatudf_eAgentSales and it should really be udf_RealEstateAgentSales according to your example.
You must assign the size for the parameters of type NVARCHAR. They were without size and I assigned a 25 for example.
The COUNT aggregate function, you must include it in your query and you can avoid having to create a variable.
The order in the WHERE section should be changed. First the field and then the parameter. However, it is not a mistake as you currently have it, but it is a bad practice.
I leave you the code I used, the enhanced function and some examples

Code:

--Purchases
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[Purchases]') AND TYPE IN (N'U'))
BEGIN
    DROP TABLE Purchases
END

CREATE TABLE Purchases 
(
    PurchaseID INT IDENTITY
    , DealMadeByEmployeeID INT
    , PurchaseValue MONEY
)

INSERT INTO Purchases ( DealMadeByEmployeeID, PurchaseValue )
VALUES                 ( 1,                    250.15        )
,                      ( 2,                    15.50         )
,                      ( 1,                    100           )
,                      ( 1,                    300.15        )
,                      ( 2,                    500.15        )


SELECT *
FROM Purchases


--Employees
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[Employees]') AND TYPE IN (N'U'))
BEGIN
    DROP TABLE Employees
END

CREATE TABLE Employees 
(
    EmployeeID INT IDENTITY
    , FirstName VARCHAR(50)
    , SecondName VARCHAR(50)
    , LastName VARCHAR(50)
)

INSERT INTO Employees ( FirstName, SecondName, LastName )
VALUES                 ( 'J',       'E',        'P'      )
,                      ( 'Mitko',   '',         'Z'      )


SELECT *
FROM Employees
GO

Now the function should be like this:

--FUNCTION
DECLARE @strSQL nvarchar(1000)
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_RealEstateAgentSales]'))
BEGIN
    SET @strSQL = 'DROP FUNCTION [dbo].[udf_RealEstateAgentSales]'
    EXEC sp_executesql @strSQL
END
GO

CREATE FUNCTION udf_RealEstateAgentSales 
    (@FirstName NVARCHAR(25)      -- Missed length
    , @SecondName NVARCHAR(25)    -- Missed length
    , @LastName NVARCHAR(25)      -- Missed length
    )
RETURNS @salesAmoutAndAgent TABLE (
    FirstName NVARCHAR(25)
    , SecondName NVARCHAR(25)
    , LastName NVARCHAR(25)
    , sales INT
    )
AS
BEGIN

    INSERT @salesAmoutAndAgent
    SELECT @FirstName
        , @SecondName
        , @LastName
        , COUNT(*) AS Sales
    FROM Purchases AS P
        INNER JOIN Employees AS E
            ON P.DealMadeByEmployeeID = E.EmployeeID
    WHERE FirstName = @FirstName
        AND SecondName = @SecondName
        AND LastName = @LastName

    RETURN

END

GO

a. Employee somename

SELECT * FROM dbo.udf_RealEstateAgentSales('somename','somename','somename')

->

FirstName                 SecondName                LastName                  sales
------------------------- ------------------------- ------------------------- -----------
somename                  somename                  somename                  0

(1 row(s) affected)

b. Employee J E P

SELECT * FROM dbo.udf_RealEstateAgentSales('J','E','P');

->

FirstName                 SecondName                LastName                  sales
------------------------- ------------------------- ------------------------- -----------
J                         E                         P                         3

(1 row(s) affected)

c. Employee Mitko Z

SELECT * FROM dbo.udf_RealEstateAgentSales('Mitko','','Z');

->

FirstName                 SecondName                LastName                  sales
------------------------- ------------------------- ------------------------- -----------
Mitko                                               Z                         2

(1 row(s) affected)

I hope you find it useful

edited Jan 3, 2017 at 18:09

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 2, 2017 at 22:26

Jota Pardo

90810 silver badges31 bronze badges

4 Comments

Jota Pardo Over a year ago

@marc_s Thank you so much for your help and Thanks for improving my answer! I want to ask you why do you delete de line with "GO" instruction? I wrote it beacause I get the error message: CREATE/ALTER PROCEDURE' must be the first statement in a query batch. For that reason I left the GO batch separator after the DROP FUNCTION block.

marc_s Over a year ago

Sorry, that was unintentional - added them back where needed

Jota Pardo Over a year ago

@marc_s Oh well! I thought it was some mistake or rule I did not know. Thank you

marc_s Over a year ago

In a great many cases, the GO is not needed, and since it's not really a SQL statement (but just a delimiter in SQL Server Management Studio), I tend to try to trim the use of GO to the needed minimum. A CREATE FUNCTION statement is one of those few cases where you must have a GO before it, in a longer script

Collectives™ on Stack Overflow

SQL Server table function doesn't return values

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related