1

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.

3 Answers 3

1

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.

0

NULL is not equal to anything, not even NULL. Try changing

WHERE (dbo.ACTIVE. RECCLS = @CompDate)

To

WHERE (dbo.ACTIVE. RECCLS = COALESCE(@CompDate, dbo.ACTIVE. RECCLS) )
-1

You have two syntax issues:

  1. A difference in the name of the function and the calling command. [dbo].[TVF_Dashboad] is not the same as TVF_DashBoard

  2. dbo.ACTIVE. RECCLS there is a space after the dot

Also the default value is NULL and it will return all the rows according to the WHERE (dbo.ACTIVE. RECCLS = @CompDate)

Where dbo.ACTIVE.RECCLS has the same value as the parameter, meaning only NULL values

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.