All geek questions in one place

SQL can you use the IF statement in UDF and how do you do it?

I am trying to create a UDF that does 2 different things depending on the time. Below is my code. I am wondering if you can use the IF statement in UDF because I am emitting 4 errors, the wrong syntax near Begin and Returns, and also the Return statement with the return value cannot be used in this context .... Any suggestions?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_TEST]
(
    @StartDate DATETIME,
    @EndDate DATETIME
)

--DECLARE @StartDate DATETIME
--DECLARE @EndDate DATETIME
--SET @StartDate = '2010-07-06 14:46:37.577' 
--SET @EndDate = '2010-07-09 09:04:31.290'
BEGIN
RETURNS VARCHAR(MAX)

(
IF (CONVERT(VARCHAR(13), @StartDate, 114) > CONVERT(VARCHAR(13), @EndDate, 114))
BEGIN
DECLARE @NonWorkTime1 INT
SET @NonWorkTime1 = 780
--How many minutes are between order start and end time including non working time
DECLARE @AllMins1 INT 
--Declares how many minutes are in a day and makes it float to get remainder minutes when divided
DECLARE @MinsInDay1 DECIMAL
SET @MinsInDay1 = 1440.0
--Finds how many minutes are between start and end time excluding weekends and assignes to variable 
SET @AllMins1 = ((DATEDIFF(mi, @StartDate, @EndDate)) 
  -(((DATEDIFF(wk, @StartDate, @EndDate) * 2) * 24) * 60)  
  -(((CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) * 24) * 60)
  -(((CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) * 24) * 60)) 
--Calculates how many days have elapsed in the minutes that the order has taken
DECLARE @MinDays1 INT
SET @MinDays1 = (@AllMins1/@MinsInDay1)
--Subtracts complete day non worked minutes from final minutes between orders
DECLARE @FinalMinutes1 AS DECIMAL
SET @FinalMinutes1 = (@AllMins1 - (@MinDays1 * @NonWorkTime1) - 360 - 420)
RETURN @FinalMinutes1
END
ELSE
BEGIN
--RETURNS VARCHAR(MAX)
--How many minutes a day are not worked for trips
DECLARE @NonWorkTime INT
SET @NonWorkTime = 780
--How many minutes are between order start and end time including non working time
DECLARE @AllMins INT 
--Declares how many minutes are in a day and makes it float to get remainder minutes when divided
DECLARE @MinsInDay DECIMAL
SET @MinsInDay = 1440.0
--Finds how many minutes are between start and end time excluding weekends and assignes to variable 
SET @AllMins = ((DATEDIFF(mi, @StartDate, @EndDate)) 
  -(((DATEDIFF(wk, @StartDate, @EndDate) * 2) * 24) * 60)  
  -(((CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) * 24) * 60)
  -(((CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) * 24) * 60)) 
--Calculates how many days have elapsed in the minutes that the order has taken
DECLARE @MinDays INT
SET @MinDays = (@AllMins/@MinsInDay)
--Subtracts complete day non worked minutes from final minutes between orders
DECLARE @FinalMinutes AS DECIMAL
SET @FinalMinutes = (@AllMins - (@MinDays * @NonWorkTime))
RETURN @FinalMinutes
END
)
END
+3
source share
2 answers

IF UDF. , , @FinalMinutes IF, .

:

CREATE FUNCTION [dbo].[udf_TEST] 
( 
    @StartDate DATETIME, 
    @EndDate DATETIME 
) 
RETURNS VARCHAR(MAX) 

--DECLARE @StartDate DATETIME 
--DECLARE @EndDate DATETIME 
--SET @StartDate = '2010-07-06 14:46:37.577'  
--SET @EndDate = '2010-07-09 09:04:31.290' 
BEGIN 
DECLARE @FinalMinutes AS DECIMAL 
IF (CONVERT(VARCHAR(13), @StartDate, 114) > CONVERT(VARCHAR(13), @EndDate, 114)) 
BEGIN 
DECLARE @NonWorkTime1 INT 
SET @NonWorkTime1 = 780 
--How many minutes are between order start and end time including non working time 
DECLARE @AllMins1 INT  
--Declares how many minutes are in a day and makes it float to get remainder minutes when divided 
DECLARE @MinsInDay1 DECIMAL 
SET @MinsInDay1 = 1440.0 
--Finds how many minutes are between start and end time excluding weekends and assignes to variable  
SET @AllMins1 = ((DATEDIFF(mi, @StartDate, @EndDate))  
  -(((DATEDIFF(wk, @StartDate, @EndDate) * 2) * 24) * 60)   
  -(((CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) * 24) * 60) 
  -(((CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) * 24) * 60))  
--Calculates how many days have elapsed in the minutes that the order has taken 
DECLARE @MinDays1 INT 
SET @MinDays1 = (@AllMins1/@MinsInDay1) 
--Subtracts complete day non worked minutes from final minutes between orders 
SET @FinalMinutes = (@AllMins1 - (@MinDays1 * @NonWorkTime1) - 360 - 420) 
END 
ELSE 
BEGIN 
--RETURNS VARCHAR(MAX) 
--How many minutes a day are not worked for trips 
DECLARE @NonWorkTime INT 
SET @NonWorkTime = 780 
--How many minutes are between order start and end time including non working time 
DECLARE @AllMins INT  
--Declares how many minutes are in a day and makes it float to get remainder minutes when divided 
DECLARE @MinsInDay DECIMAL 
SET @MinsInDay = 1440.0 
--Finds how many minutes are between start and end time excluding weekends and assignes to variable  
SET @AllMins = ((DATEDIFF(mi, @StartDate, @EndDate))  
  -(((DATEDIFF(wk, @StartDate, @EndDate) * 2) * 24) * 60)   
  -(((CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) * 24) * 60) 
  -(((CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) * 24) * 60))  
--Calculates how many days have elapsed in the minutes that the order has taken 
DECLARE @MinDays INT 
SET @MinDays = (@AllMins/@MinsInDay) 
--Subtracts complete day non worked minutes from final minutes between orders 
SET @FinalMinutes = (@AllMins - (@MinDays * @NonWorkTime)) 
END
RETURN @FinalMinutes
END
+3

RETURNS BEGIN

@FinalMinutes1 IF

RETURN

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_TEST]
(
    @StartDate DATETIME,
    @EndDate DATETIME
)

--DECLARE @StartDate DATETIME
--DECLARE @EndDate DATETIME
--SET @StartDate = '2010-07-06 14:46:37.577' 
--SET @EndDate = '2010-07-09 09:04:31.290'
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @FinalMinutes1 AS DECIMAL

IF (CONVERT(VARCHAR(13), @StartDate, 114) > CONVERT(VARCHAR(13), @EndDate, 114))
BEGIN
DECLARE @NonWorkTime1 INT
SET @NonWorkTime1 = 780
--How many minutes are between order start and end time including non working time
DECLARE @AllMins1 INT 
--Declares how many minutes are in a day and makes it float to get remainder minutes when divided
DECLARE @MinsInDay1 DECIMAL
SET @MinsInDay1 = 1440.0
--Finds how many minutes are between start and end time excluding weekends and assignes to variable 
SET @AllMins1 = ((DATEDIFF(mi, @StartDate, @EndDate)) 
  -(((DATEDIFF(wk, @StartDate, @EndDate) * 2) * 24) * 60)  
  -(((CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) * 24) * 60)
  -(((CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) * 24) * 60)) 
--Calculates how many days have elapsed in the minutes that the order has taken
DECLARE @MinDays1 INT
SET @MinDays1 = (@AllMins1/@MinsInDay1)
--Subtracts complete day non worked minutes from final minutes between orders

SET @FinalMinutes1 = (@AllMins1 - (@MinDays1 * @NonWorkTime1) - 360 - 420)
RETURN @FinalMinutes1
END
ELSE
BEGIN
--RETURNS VARCHAR(MAX)
--How many minutes a day are not worked for trips
DECLARE @NonWorkTime INT
SET @NonWorkTime = 780
--How many minutes are between order start and end time including non working time
DECLARE @AllMins INT 
--Declares how many minutes are in a day and makes it float to get remainder minutes when divided
DECLARE @MinsInDay DECIMAL
SET @MinsInDay = 1440.0
--Finds how many minutes are between start and end time excluding weekends and assignes to variable 
SET @AllMins = ((DATEDIFF(mi, @StartDate, @EndDate)) 
  -(((DATEDIFF(wk, @StartDate, @EndDate) * 2) * 24) * 60)  
  -(((CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) * 24) * 60)
  -(((CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) * 24) * 60)) 
--Calculates how many days have elapsed in the minutes that the order has taken
DECLARE @MinDays INT
SET @MinDays = (@AllMins/@MinsInDay)
--Subtracts complete day non worked minutes from final minutes between orders
DECLARE @FinalMinutes AS DECIMAL
SET @FinalMinutes = (@AllMins - (@MinDays * @NonWorkTime))

END
RETURN @FinalMinutes
END
+1

Source: https://habr.com/ru/post/1758950/

More articles:


All Articles