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
CodePudding user response:
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';