Sql-server – Tabled Value Function returns incorrect or no results

functionssql server

I recently learned of TVF's and it seemed like it was exactly what I was looking for. I did my first attempt at one and it is not going well.

Here is my code:

USE [RMC_Tracker] GO
/****** Object:    UserDefinedFunction [dbo].[TFV_Dashboard]
9:08:08 AM ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO



Script Date: 4/13/2020

--------------------------------------------
Author:    John Clark
Create date: 4/12/2020
Description: Returns a recordset for the RMC Tracker

ALTER FUNCTION [dbo].[TVF_Dashboad]
(
-- Add the parameters for the function here
@CompDate datetime = NULL

)
RETURNS TABLE AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT    dbo.ACTIVE.RECORD_ID, dbo.ACTIVE.CMD_ID, dbo.ACTIVE.EQUIPMENT, dbo.ACTIVE.EquipDes, 
          dbo.ACTIVE.CASREP,dbo.ACTIVE.[UPDATE], dbo.ACTIVE.TYAST,     
          dbo.ACTIVE.STATUS,dbo.ACTIVE.JCN, dbo.COMMANDS.Trigrpah

FROM      dbo.ACTIVE INNER JOIN dbo.COMMANDS ON dbo.ACTIVE.CMD_ID = dbo.COMMANDS.CMD ID


WHERE    (dbo.ACTIVE. RECCLS = @CompDate)
)

I call this Function from within MS Access via a Pass Through Query:

SELECT * FROM dbo.TVF_DashBoard(default)

It returns an empty set. What I am trying to do is create a simple Function that allows me to return a table that can be toggle based on whether a field (RECCLS) is either Null or Not Null.

Any assistance would be appreciated.

Best Answer

The default value of @CompDate is NULL. I suppose you actually want to find rows where dbo.ACTIVE.RECCLS IS NULL. So you need to modify your function code:

ALTER FUNCTION [dbo].[TVF_Dashboad]
(
-- Add the parameters for the function here
@CompDate datetime = NULL

)
RETURNS TABLE AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT    dbo.ACTIVE.RECORD_ID, dbo.ACTIVE.CMD_ID, dbo.ACTIVE.EQUIPMENT, dbo.ACTIVE.EquipDes, 
          dbo.ACTIVE.CASREP,dbo.ACTIVE.[UPDATE], dbo.ACTIVE.TYAST,     
          dbo.ACTIVE.STATUS,dbo.ACTIVE.JCN, dbo.COMMANDS.Trigrpah

FROM      dbo.ACTIVE INNER JOIN dbo.COMMANDS ON dbo.ACTIVE.CMD_ID = dbo.COMMANDS.CMD ID


WHERE    (dbo.ACTIVE.RECCLS = @CompDate)
    OR (@CompDate IS NULL AND dbo.ACTIVE.RECCLS IS NULL)
)

Notice: such function may suffer from parameter sniffing issue.

Related Question