Convert stored procedure to function

Question

I need to make use of a function to be able to consume it through Power BI due to the Direct Query data connectivity mode, there is no way that Power BI can make use of stored procedures.

Therefore I am trying to create a function from a stored procedure.

The following is the stored procedure from which I want to create the function:

ALTER PROCEDURE [dbo].[SPTest]   
    @Anio int,
    @Mes int
AS
BEGIN
    DECLARE @AnioMes  varchar(8),
            @AnioMes6 varchar(8)

    IF @Anio IS NULL
        SELECT @Anio = YEAR(GETDATE()),
               @Mes  = MONTH(GETDATE())

    SELECT 
        @AnioMes = (CASE WHEN @Mes = 12 THEN @Anio + 1 ELSE @Anio END * 100 + 
                    CASE WHEN @Mes = 12 THEN 1 ELSE @Mes + 1 END) * 100 + 1
    SELECT
        @AnioMes6 = CONVERT(varchar(8), DATEADD(mm, -5, @AnioMes), 112)

    SELECT 
        YEAR(DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME)) * 100 + 
        MONTH(DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME)) AS AnioMes,
        DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME) AS FECHA_CREACION,
        INPUTSUBJECT,
        CASE 
            WHEN TOWTYPE = 1 THEN 'T1'
            WHEN TOWTYPE = 2 THEN 'T2'
            WHEN TOWTYPE = 3 THEN 'T3'
            WHEN TOWTYPE = 4 THEN 'T4'
            ELSE ''
        END AS TOWTYPE,
        [VENDNAME]
    FROM 
        TRUCKS
    WHERE
        YEAR(DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME)) >= yEAR(@AnioMes6)
        AND MONTH(DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME)) <= MONTH(@AnioMes)
        AND DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME) >= '20160701'

Hope someone can give me some guidance on how to create a function from this stored procedure

I have tried to do the following:

CREATE FUNCTION [dbo].[SPTest]
(
    @Anio   int,
    @Mes    int,
    @AnioMes    varchar(8),
    @AnioMes6   varchar(8)
)
RETURNS TABLE
AS
BEGIN

        if @Anio is null
        Select  @Anio   = YEAR(GETDATE()),
                @Mes    = MONTH(GETDATE())

    Select  @AnioMes    = (case when @Mes=12 then @Anio+1 else @Anio end *100 + Case when @Mes=12 then 1 else @Mes+1 end)*100 + 1
    Select  @AnioMes6   = convert(varchar(8), DATEADD(mm, -5, @AnioMes), 112 )


    SELECT year(DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME))*100+month(DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME)) as AnioMes
          ,DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME) as FECHA_CREACION
          ,INPUTSUBJECT
          ,Case When TOWTYPE = 1 then 'T1'
                When TOWTYPE = 2 then 'T2'
                When TOWTYPE = 3 then 'T3'
                When TOWTYPE = 4 then 'T4'
                Else ''
            End as TOWTYPE
          ,[VENDNAME]
        FROM TRUCKS
        Where   year(DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME)) >= yEAR(@AnioMes6)
        and month(DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME)) <= MONTH(@AnioMes)
        AND     DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME) >= '20160701'

    END 

    RETURN

I get the following error in the BEGIN:

Incorrect syntax near BEGIN

user_14868997 · Answer

First off, you should always try to stick to inline Table Valued Functions, as they perform far better.

An inline function has no BEGIN or END and can only contain a single AS RETURN SELECT statement.

Note how the variables have been converted into VALUES clauses
Avoid using functions against columns. Instead of converting CREATEDDATETIME to a time zone to compare, convert your parameters to UTC
For the same reason, don't compare parts of a date, construct the start and end points and compare against that
Don't use string conversions to create dates, it's slow and can be non-deterministic. Instead use the proper date functions available

CREATE OR ALTER FUNCTION [dbo].[SPTest] (
    @month DATETIME
)
RETURNS TABLE
AS RETURN

SELECT
   v1.AnioMes
  ,t.CREATEDDATETIME AT TIME ZONE 'YourTimeZoneHere' as FECHA_CREACION
  ,t.INPUTSUBJECT
  ,CASE WHEN t.TOWTYPE IN (1,2,3,4)
        THEN CONCAT('T', t.TOWTYPE)
        ELSE '' END AS TOWTYPE
  ,t.[VENDNAME]

FROM (VALUES (
    DATEADD(day, 1, EOMONTH(TODATETIMEOFFSET(@month, 0)))
)) v1(AnioMes)

CROSS APPLY (VALUES (
    DATEADD(month, -5, v1.AnioMes)
)) v2(AnioMes6)

CROSS JOIN TRUCKS t

WHERE t.CREATEDDATETIME >= v2.AnioMes6
  AND t.CREATEDDATETIME <  v1.AnioMes
  AND t.CREATEDDATETIME >= '20160701';

Convert stored procedure to function

1 Answers