36 lines
1.3 KiB
Transact-SQL
36 lines
1.3 KiB
Transact-SQL
USE [VEPROMS]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[vefn_CompareTranFormat] Script Date: 02/26/2010 07:05:49 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
/*
|
|
Determines if two transition formats are same string.
|
|
returns 0 if identical, 1 if similar (range for range, item for item), 2 if totally different
|
|
*/
|
|
ALTER FUNCTION [dbo].[vefn_CompareTranFormat] (@FormatID int, @NewFormatID int, @TranType int) RETURNS int
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
BEGIN
|
|
--
|
|
IF @FormatID = @NewFormatID
|
|
RETURN @TranType
|
|
DECLARE @TransFormat varchar(max)
|
|
DECLARE @NewTransFormat varchar(max)
|
|
SET @TransFormat = isnull(.dbo.vefn_GetTransFormat(@FormatID, @TranType),'')
|
|
SET @NewTransFormat = isnull(.dbo.vefn_GetTransFormat(@NewFormatID, @TranType),'')
|
|
RETURN CASE
|
|
WHEN @TransFormat = @NewTransFormat THEN 0 -- transition formats are identical
|
|
WHEN @TransFormat LIKE '%{Last Step}%' THEN
|
|
CASE
|
|
WHEN @NewTransFormat LIKE '%{Last Step}%' THEN 1 -- both ranges, but different format
|
|
ELSE 2 -- totally different format, not even same type (was single, changed to range)
|
|
END
|
|
ELSE
|
|
CASE
|
|
WHEN @NewTransFormat LIKE '%{Last Step}%' THEN 2 -- totally different format, not even same type (was range, changed to single)
|
|
ELSE 1 -- both single, but different format
|
|
END
|
|
END
|
|
END |