Function - help 2

sparkbyte · Jun 23, 2010

I have a function that returns a table-value.

What i need to do is account for the user not putting in any values and setting defaults.

There are three parameter values that are passed to the function.

Starting Date
Ending Date
Employee Last Name

If the starting date is not supplied I would like it to default to the beginning of the current day.

if Ending Date is not supplied it should default to the close of the current day.

If the employee name is not given it should default to all employees.

Here is the code for the function.

Code:

USE [MailroomTracking]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_QC_Employee_Report]    Script Date: 06/23/2010 12:34:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		John Fuhrman
-- Create date: 2010/06/22
-- Description:	QC Report
-- =============================================
ALTER FUNCTION [dbo].[fn_QC_Employee_Report] 
(	
	-- Add the parameters for the function here
	@Enter_Starting_Date DateTime, 
	@Enter_Ending_Date DateTime,
	@Enter_Employee_Last_Name VarChar(50)
)
RETURNS TABLE 
AS
RETURN 
(
	-- Add the SELECT statement with parameter references here
SELECT     TOP (100) PERCENT 
		dbo.tblTrackingTable.Tracking_ID, 
		EmployeeFullName = Case
				When EmployeeMI = '' Then EmployeeLN + ', ' + EmployeeFN
				When EmployeeMI Is Null Then EmployeeLN + ', ' + EmployeeFN
				When EmployeeMI <> '' Then EmployeeLN + ', ' + EmployeeFN + ' ' + EmployeeMI
			End,
		dbo.tblEmployee.EmployeeFN, 
		dbo.tblEmployee.EmployeeLN, 
		dbo.tblTrackingTable.EmployeeID, 
		dbo.tblTrackingTable.MachineName, 
		UPPER(dbo.tblTrackingTable.BoxNumber) AS BoxNumber, 
		UPPER(dbo.tblTrackingTable.FileNumber) AS FileNumber, 
		dbo.tblTrackingTable.TrackingDate

FROM         dbo.tblTrackingTable FULL OUTER JOIN
                      dbo.tblEmployee ON dbo.tblTrackingTable.EmployeeID = dbo.tblEmployee.EmployeeID

WHERE     (dbo.tblTrackingTable.TrackingDate 
		BETWEEN DATEADD(dd, DATEDIFF(dd, 0, @Enter_Starting_Date), 0) 
		AND DATEADD(ss, - 1, DATEADD(dd, DATEDIFF(dd, 0, @Enter_Ending_Date), 1)))
		AND (dbo.tblEmployee.EmployeeLN Like '%' + @Enter_Employee_Last_Name + '%')
		AND (dbo.tblTrackingTable.EmployeeID IS NOT NULL) 
		AND (dbo.tblTrackingTable.FileNumber <> '') 
		AND (dbo.tblTrackingTable.BoxNumber <> '') 
		AND (dbo.tblTrackingTable.FileNumber <> '.BOX.END.') 
		AND (dbo.tblTrackingTable.TrackingDate IS NOT NULL)

ORDER BY dbo.tblTrackingTable.TrackingDate, BoxNumber
)

Thanks

John Fuhrman

sparkbyte · Jun 23, 2010

I have tried this but get acouple of errors

Code:

USE [MailroomTracking]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_QC_Employee_Report]    Script Date: 06/23/2010 12:34:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		John Fuhrman
-- Create date: 2010/06/22
-- Description:	QC Report
-- =============================================
ALTER FUNCTION [dbo].[fn_QC_Employee_Report] 
(	
	-- Add the parameters for the function here
	@Enter_Starting_Date DateTime[red] = DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)[/red], 
	@Enter_Ending_Date DateTime[red] = DATEADD(ss, - 1, DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 1))[/red],
	@Enter_Employee_Last_Name VarChar(50)[red] = '%'[/red]
)
RETURNS TABLE 
AS
RETURN 
(
	-- Add the SELECT statement with parameter references here
SELECT     TOP (100) PERCENT 
		dbo.tblTrackingTable.Tracking_ID, 
		EmployeeFullName = Case
				When EmployeeMI = '' Then EmployeeLN + ', ' + EmployeeFN
				When EmployeeMI Is Null Then EmployeeLN + ', ' + EmployeeFN
				When EmployeeMI <> '' Then EmployeeLN + ', ' + EmployeeFN + ' ' + EmployeeMI
			End,
		dbo.tblEmployee.EmployeeFN, 
		dbo.tblEmployee.EmployeeLN, 
		dbo.tblTrackingTable.EmployeeID, 
		dbo.tblTrackingTable.MachineName, 
		UPPER(dbo.tblTrackingTable.BoxNumber) AS BoxNumber, 
		UPPER(dbo.tblTrackingTable.FileNumber) AS FileNumber, 
		dbo.tblTrackingTable.TrackingDate

FROM         dbo.tblTrackingTable FULL OUTER JOIN
                      dbo.tblEmployee ON dbo.tblTrackingTable.EmployeeID = dbo.tblEmployee.EmployeeID

WHERE     (dbo.tblTrackingTable.TrackingDate 
		BETWEEN DATEADD(dd, DATEDIFF(dd, 0, @Enter_Starting_Date), 0) 
		AND DATEADD(ss, - 1, DATEADD(dd, DATEDIFF(dd, 0, @Enter_Ending_Date), 1)))
		AND (dbo.tblEmployee.EmployeeLN Like '%' + @Enter_Employee_Last_Name + '%')
		AND (dbo.tblTrackingTable.EmployeeID IS NOT NULL) 
		AND (dbo.tblTrackingTable.FileNumber <> '') 
		AND (dbo.tblTrackingTable.BoxNumber <> '') 
		AND (dbo.tblTrackingTable.FileNumber <> '.BOX.END.') 
		AND (dbo.tblTrackingTable.TrackingDate IS NOT NULL)

ORDER BY dbo.tblTrackingTable.TrackingDate, BoxNumber
)

Error:

[red]
Msg 102, Level 15, State 1, Procedure fn_QC_Employee_Report, Line 9
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure fn_QC_Employee_Report, Line 37
Must declare the scalar variable "@Enter_Starting_Date".
[/red]

Thanks

John Fuhrman

RiverGuy · Jun 23, 2010

When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called to retrieve the default value. This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value.

http://msdn.microsoft.com/en-us/library/ms186755.aspx

sparkbyte · Jun 23, 2010

Ok, I got that.

fn_QC_Employee_Report(DEFAULT,DEFAULT,DEFAULT)

Would run the function using all default values.

How do I assign the default values?

Thanks

John Fuhrman

sparkbyte · Jun 23, 2010

MS Example

Code:

CREATE PROCEDURE HumanResources.uspGetEmployees2 
    @LastName nvarchar(50) = N'D%', 
    @FirstName nvarchar(50) = N'%'
AS 
    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

My Code

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		John Fuhrman
-- Create date: 2010/06/22
-- Description:	QC Report
-- =============================================

CREATE PROCEDURE usp_QC_Employee_Report 
	-- Add the parameters for the stored procedure here

	@Enter_Starting_Date DateTime = CURRENT_TIMESTAMP, 
	@Enter_Ending_Date DateTime = CURRENT_TIMESTAMP,
	@Enter_Employee_Last_Name VarChar(50)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

SELECT     TOP (100) PERCENT 
		dbo.tblTrackingTable.Tracking_ID, 
		EmployeeFullName = Case
				When EmployeeMI = '' Then EmployeeLN + ', ' + EmployeeFN
				When EmployeeMI Is Null Then EmployeeLN + ', ' + EmployeeFN
				When EmployeeMI <> '' Then EmployeeLN + ', ' + EmployeeFN + ' ' + EmployeeMI
			End,
		dbo.tblEmployee.EmployeeFN, 
		dbo.tblEmployee.EmployeeLN, 
		dbo.tblTrackingTable.EmployeeID, 
		dbo.tblTrackingTable.MachineName, 
		UPPER(dbo.tblTrackingTable.BoxNumber) AS BoxNumber, 
		UPPER(dbo.tblTrackingTable.FileNumber) AS FileNumber, 
		dbo.tblTrackingTable.TrackingDate

FROM         dbo.tblTrackingTable FULL OUTER JOIN
                      dbo.tblEmployee ON dbo.tblTrackingTable.EmployeeID = dbo.tblEmployee.EmployeeID

WHERE     (dbo.tblTrackingTable.TrackingDate 
		BETWEEN DATEADD(dd, DATEDIFF(dd, 0, @Enter_Starting_Date), 0) 
		AND DATEADD(ss, - 1, DATEADD(dd, DATEDIFF(dd, 0, @Enter_Ending_Date), 1)))
		AND (dbo.tblEmployee.EmployeeLN Like '%' + @Enter_Employee_Last_Name + '%')
		AND (dbo.tblTrackingTable.EmployeeID IS NOT NULL) 
		AND (dbo.tblTrackingTable.FileNumber <> '') 
		AND (dbo.tblTrackingTable.BoxNumber <> '') 
		AND (dbo.tblTrackingTable.FileNumber <> '.BOX.END.') 
		AND (dbo.tblTrackingTable.TrackingDate IS NOT NULL)

ORDER BY dbo.tblTrackingTable.TrackingDate, BoxNumber

END
GO

[red]
Msg 156, Level 15, State 1, Procedure usp_QC_Employee_Report, Line 11
Incorrect syntax near the keyword 'CURRENT_TIMESTAMP'.
Msg 137, Level 15, State 2, Procedure usp_QC_Employee_Report, Line 40
Must declare the scalar variable "@Enter_Starting_Date".
[/red]

What have I missed??????

Thanks

John Fuhrman

bborissov · Jun 23, 2010

Code:

CREATE FUNCTION [dbo].[fn_QC_Employee_Report]
(    
    -- Add the parameters for the function here
    @Enter_Starting_Date DateTime = NULL,
    @Enter_Ending_Date DateTime = NULL,
    @Enter_Employee_Last_Name VarChar(50) = ''
)

--- Just change the table structure with the correct field types
RETURNS @retFindReports TABLE 
(
        Tracking_ID int,
        EmployeeFullName varchar(8000),
        EmployeeFN varchar(8000),
        EmployeeLN varchar(8000),
        EmployeeID varchar(8000),
        MachineName varchar(8000),
        BoxNumber varchar(8000),
        FileNumber varchar(8000),
        TrackingDate datetime
)

AS
 BEGIN
      IF @Enter_Starting_Date IS NULL
         SET @Enter_Starting_Date = DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)
 
      IF @Enter_Ending_Date IS NULL
         SET @Enter_Ending_Date =  DATEADD(ss, - 1, DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 1))

    -- Add the SELECT statement with parameter references here
    INSERT INTO  @retFindReports
    SELECT dbo.tblTrackingTable.Tracking_ID,
           EmployeeFullName = Case
                                 When EmployeeMI = '' Then EmployeeLN + ', ' + EmployeeFN
                                 When EmployeeMI Is Null Then EmployeeLN + ', ' + EmployeeFN
                                 When EmployeeMI <> '' Then EmployeeLN + ', ' + EmployeeFN + ' ' + EmployeeMI
                              End,
           dbo.tblEmployee.EmployeeFN,
           dbo.tblEmployee.EmployeeLN,
           dbo.tblTrackingTable.EmployeeID,
           dbo.tblTrackingTable.MachineName,
           UPPER(dbo.tblTrackingTable.BoxNumber) AS BoxNumber,
           UPPER(dbo.tblTrackingTable.FileNumber) AS FileNumber,
           dbo.tblTrackingTable.TrackingDate
    FROM dbo.tblTrackingTable
    FULL JOIN dbo.tblEmployee ON dbo.tblTrackingTable.EmployeeID = dbo.tblEmployee.EmployeeID
    WHERE (dbo.tblTrackingTable.TrackingDate BETWEEN DATEADD(dd, DATEDIFF(dd, 0, @Enter_Starting_Date), 0)
        AND DATEADD(ss, - 1, DATEADD(dd, DATEDIFF(dd, 0, @Enter_Ending_Date), 1)))
        AND (dbo.tblEmployee.EmployeeLN Like '%' + @Enter_Employee_Last_Name + '%')
        AND (dbo.tblTrackingTable.EmployeeID IS NOT NULL)
        AND (dbo.tblTrackingTable.FileNumber <> '')
        AND (dbo.tblTrackingTable.BoxNumber <> '')
        AND (dbo.tblTrackingTable.FileNumber <> '.BOX.END.')
        AND (dbo.tblTrackingTable.TrackingDate IS NOT NULL)
   ORDER BY dbo.tblTrackingTable.TrackingDate, BoxNumber
   RETURN

 END

NOT TESTED!!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

gmmastros · Jun 23, 2010

That's some mighty fine varchar'n there Boris. [wink]

Alternatively... you could tweak your where clause a little, like this...

Code:

    WHERE (@Enter_StartingDate Is NULL OR dbo.tblTrackingTable.TrackingDate >=DATEADD(dd, DATEDIFF(dd, 0, @Enter_Starting_Date), 0))
        AND (@Enter_Ending_Date Is NULL Or dbo.tblTrackingTable.TrackingDate < DATEADD(dd, DATEDIFF(dd, 0, @Enter_Ending_Date), 1))
        AND (@Endter_Employee_Last_Name Is NULL OR dbo.tblEmployee.EmployeeLN Like '%' + @Enter_Employee_Last_Name + '%')
        AND (dbo.tblTrackingTable.EmployeeID IS NOT NULL)
        AND (dbo.tblTrackingTable.FileNumber <> '')
        AND (dbo.tblTrackingTable.BoxNumber <> '')
        AND (dbo.tblTrackingTable.FileNumber <> '.BOX.END.')
        AND (dbo.tblTrackingTable.TrackingDate IS NOT NULL)

Everything else would be the same from your original code posting in this thread. The OR's in the where clause will do the trick for you. This really isn't any different than someone asking, "I want optional parameters for a report I'm building". Since you can't do dynamic sql in a function, the only choice (in my opinion) is the OR's in the where clause.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

bborissov · Jun 23, 2010

George, that is not the same as the original question

If the starting date is not supplied I would like it to default to the beginning of the current day.

if Ending Date is not supplied it should default to the close of the current day.

With (@... IS NULL OR ...) we just skip the Parameter.

(or I'm missing something?)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

markros · Jun 23, 2010

You need to simply use COALESCE function with the dates in the criteria. Also, Boris, you changed in-line function to multi-line function. The idea is to keep it inline.

PluralSight Learning Library

gmmastros · Jun 23, 2010

Boris, you're right. I misunderstood.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

bborissov · Jun 23, 2010

markros,

Why should it be inline?
This is not C++

But you are right, you could use COALESCE() in the WHERE clause.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

markros · Jun 24, 2010

Boris,

There is a big difference in performance in inline table valued functions (just return statement with the table) or with multi-line functions. Inline functions perform many times better than multi-line functions.

PluralSight Learning Library

bborissov · Jun 24, 2010

Are you sure?
I didn't knew that.
I try to avoid Functions when I could.
Where I could read about it?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

markros · Jun 24, 2010

I'm pretty sure though I learned it quite recently. Mostly from reading answers on MSDN T-SQL forum.

PluralSight Learning Library

markros · Jun 24, 2010

I've run Google search.

http://www.sqlmag.com/article/tsql3/inline-vs-multistatement-table-valued-udfs.aspx

(unfortunately, this is a paid article)

http://ask.sqlservercentral.com/questions/1549/what-are-the-advantages-of-each-type-of-udf

http://sqlserverpedia.com/blog/sql-...d-function-to-a-inline-table-valued-function/

(I know what Brad Schulz and Adam Haines are big advocates of inline TVFs).

PluralSight Learning Library

bborissov · Jun 24, 2010

Thank you!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

sparkbyte · Jun 24, 2010

Borislav that worked.

markros

You need to simply use COALESCE function with the dates in the criteria. Also, Boris, you changed in-line function to multi-line function. The idea is to keep it inline.

I have not figured out how to properly use the COALESCE function. How would it be applied here?

Thanks!!

Thanks

John Fuhrman

markros · Jun 24, 2010

Code:

CREATE FUNCTION [dbo].[fn_QC_Employee_Report]
(    
    -- Add the parameters for the function here
    @Enter_Starting_Date DateTime = NULL,
    @Enter_Ending_Date DateTime = NULL,
    @Enter_Employee_Last_Name VarChar(50) = ''
)

--- Just change the table structure with the correct field types
RETURNS TABLE 
(
        Tracking_ID int,
        EmployeeFullName varchar(8000),
        EmployeeFN varchar(8000),
        EmployeeLN varchar(8000),
        EmployeeID varchar(8000),
        MachineName varchar(8000),
        BoxNumber varchar(8000),
        FileNumber varchar(8000),
        TrackingDate datetime
)

AS
 return
    SELECT dbo.tblTrackingTable.Tracking_ID,
           EmployeeFullName = Case
                                 When EmployeeMI = '' Then EmployeeLN + ', ' + EmployeeFN
                                 When EmployeeMI Is Null Then EmployeeLN + ', ' + EmployeeFN
                                 When EmployeeMI <> '' Then EmployeeLN + ', ' + EmployeeFN + ' ' + EmployeeMI
                              End,
           dbo.tblEmployee.EmployeeFN,
           dbo.tblEmployee.EmployeeLN,
           dbo.tblTrackingTable.EmployeeID,
           dbo.tblTrackingTable.MachineName,
           UPPER(dbo.tblTrackingTable.BoxNumber) AS BoxNumber,
           UPPER(dbo.tblTrackingTable.FileNumber) AS FileNumber,
           dbo.tblTrackingTable.TrackingDate
    FROM dbo.tblTrackingTable
    FULL JOIN dbo.tblEmployee ON dbo.tblTrackingTable.EmployeeID = dbo.tblEmployee.EmployeeID
    WHERE (dbo.tblTrackingTable.TrackingDate BETWEEN DATEADD(dd, DATEDIFF(dd, 0, COALESCE(@Enter_Starting_Date,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0))
 
      
        AND DATEADD(ss, - 1, DATEADD(dd, DATEDIFF(dd, 0, COALESCE(@Enter_Ending_Date,DATEADD(ss, - 1, DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 1))
), 0)), 1)))
        AND (dbo.tblEmployee.EmployeeLN Like '%' + @Enter_Employee_Last_Name + '%')
        AND (dbo.tblTrackingTable.EmployeeID IS NOT NULL)
        AND (dbo.tblTrackingTable.FileNumber <> '')
        AND (dbo.tblTrackingTable.BoxNumber <> '')
        AND (dbo.tblTrackingTable.FileNumber <> '.BOX.END.')
        AND (dbo.tblTrackingTable.TrackingDate IS NOT NULL)
   ORDER BY dbo.tblTrackingTable.TrackingDate, BoxNumber
   RETURN

 END

Something like the above (I replaced dates with their coalesce equivalent)

PluralSight Learning Library

sparkbyte · Jun 25, 2010

OK, I am getting a syntax error.

[red]
Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'AND'.
[/red]

Here is as far as I get tracing the logic.

Code:

Declare
    @Enter_Starting_Date DateTime,
    @Enter_Ending_Date DateTime,
    @Enter_Employee_Last_Name VarChar(50)

Set @Enter_Starting_Date = '06/14/2010'
Set @Enter_Ending_Date = '06/14/2010'
Set @Enter_Employee_Last_Name = 'fuhrman'


Select DATEDIFF(dd, 0, CURRENT_TIMESTAMP)
Select DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP),1)
Select DATEADD(ss, - 1, DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP),1))
Select COALESCE(@Enter_Ending_Date, DATEADD(ss, - 1, DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP),1)),0)

40352
2010-06-26 00:00:00.000
2010-06-25 23:59:59.000
2010-06-14 00:00:00.000

Thanks

John Fuhrman

markros · Jun 25, 2010

I wrote the code based on the function Boris wrote. Can you fix the typos if any? I was just showing you the idea - in your code you use one return statement and just use the last coalesce statement instead of plain @date (e.g. instead of separate assignments as in Boris code you do inline assignments - that's all).

PluralSight Learning Library

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Function - help 2

Technical User

Technical User

Programmer

Technical User

Technical User

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor