K
The logic of the procedure is very strange. You're comparing years and months separately instead of comparing full dates. You are also comparing UTC dates with local dates. For practical purposes, I removed everything that seemed meaningless (including conversions to wrong data types) and focused on changing the procedure into a function.It is very important (and basic) to understand the difference between a function and a stored procedure. The function is made to return something (a scaling value or a set of data) while a procedure is designed to perform a process that can end the return of data sets and values but it is not necessary.Having said the above, it is important to mention that the functions (especially the table type) with multiple instructions are a performance problem that can generate bottlenecks in a system that cannot be corrected. The table-like functions of a single instruction do not have that problem, so I present such a function.CREATE Function [dbo].[AlgunaFuncion](
@Anio int,
@Mes int
)
RETURNS TABLE
AS
RETURN
WITH cteAnioMes AS(
SELECT ISNULL( DATEADD( MM, 1, DATEFROMPARTS( @Anio, @Mes, 1)), DATEDIFF( MM, DATEDIFF( MM, 0, GETDATE()) + 1, 0)) AnioMes
)
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 t
JOIN cteAnioMes ON CREATEDDATETIME >= DATEADD( MM, -5, x.AnioMes)
AND CREATEDDATETIME < x.AnioMes
WHERE CREATEDDATETIME >= '20160701'
GO
