?
Solved

Convert Query to TSQL UDF

Posted on 2012-04-12
5
Medium Priority
?
397 Views
Last Modified: 2012-08-13
I'm havin an issue converting a working query to a sql server user defined function.
The function takes two dates and returns a table with the difference between the two in each date part no matter how the dates are formatted.
For example if the dates were 1992-09-06 00:00:00 and 19941212 00:00:00 it would return

years    months   days    hours     minutes
---------   ------------    -------    --------     --------------
   2           3             6         0            0

It also returns a specific part if you specify it as a third parameter.

This query is working perfectly as a stand alone query but when I try to put it into a UDF I get the error message: "Incorrect syntax near 'BEGIN'.

Attached are the working query and the UDF.

Can someone let me know what I'm doing wrong?
getFullAgeFromTwoDates2.txt
getFullAgeFromTwoDates2-UDF.txt
0
Comment
Question by:Garbonzo_Horowitz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 6

Accepted Solution

by:
SJCFL-Admin earned 1600 total points
ID: 37838833
I do not think you are clear on user defined functions.  You need to define what is being returned and then select the values into the defined fields.  I've partly modified the code. You can see where the returns clause needs to describe what is being returned.  (You need to add the types varchar, whatever)    Then down below in The select, you should precede it with an insert so that the results are placed into the returned table structure for the udf to pass back to the code from whence it was called.....  you were almost there. Just that little piece missing.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            
-- Create date: 4/12/2012
-- Description:      Returns date diff parts between 2 given dates
-- =============================================
CREATE FUNCTION dbo.termDiff
(
      @dOne datetime,
      @dDos datetime,
      @thePart varchar(15)
)
RETURNS @MyTable TABLE
(
years ....,
months.....,
days....,
hours....,
minutes....
)
AS
BEGIN -- main

      -- Fill the table variable with the rows for your result set
IF @thePart = 'all' OR @thePart = ''
BEGIN
insert into @MyTable
select
years, months,
case
      when day(@dOne)>day(@dDos)
      then day(@dDos)+
      datediff(day,@dOne,dateadd(month,datediff(month,0,@dOne)+1,0))-1
      else day(@dDos)-day(@dOne)
end as days,

datepart(hour,convert(varchar(10),dateadd(minute,minutes,0),108)) as hours,
datepart(minute,convert(varchar(10),dateadd(minute,minutes,0),108)) as minutes

from
(
      select
      years,
      datediff(month,dateadd(year,years,@dOne),@dDos)+
      case
            when day(@dDos)>=day(@dOne)
            then 0
            else -1
      end as months,
      
      datediff(minute,convert(varchar(8),@dOne,108),
      convert(varchar(8),@dDos,108)) as minutes
      from
      (
            select
            datediff(year,@dOne,@dDos) +
            case
                  when month(@dDos)>=month(@dOne)
                  then 0
                  else -1
            end as years
      ) as t
) as t

END
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 37840773
In other words, if you want to use your SQL script your UDF cannot be an Inline function as you have coded it, it is to be a Multi-Statement function.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37843163
ac, would you say this is an appropriate representation for the two different types of functions?

Inline Function:  This is very simple. The returned table is not explicitly defined. the function is expressed as a basic select statement.

Multi-Statement function: More complicated (and can be less efficient). Must define the table to be returned and populate it's values within your code. Must include a BEGIN/END block.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 37844122
That looks right to me.  Though I would add:  In general and for performance reasons, Multi-statement UDFs should be avoided like the plague. :)

But I think you said that...
0
 

Author Closing Comment

by:Garbonzo_Horowitz
ID: 37861386
Thanks. Sorry for the delay in getting back to this question.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question