36 lines
1.1 KiB
Transact-SQL
36 lines
1.1 KiB
Transact-SQL
USE [VEPROMS]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[ve_GetTransFormat] Script Date: 02/25/2010 09:18:41 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
/* Samples
|
|
select .dbo.vefn_GetTransFormat(1,6)
|
|
select .dbo.vefn_GetTransFormat(1,1)
|
|
select .dbo.vefn_GetTransFormat(1,2)
|
|
select .dbo.vefn_GetTransFormat(1,3)
|
|
|
|
*/
|
|
ALTER FUNCTION [dbo].[vefn_GetTransFormat] (@FormatID int, @TranType int) RETURNS varchar(max)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
BEGIN
|
|
|
|
-- Walk up the Formats (inheritance) looking for the TransFormat
|
|
Declare @TransFormat as varchar(MAX)
|
|
BEGIN
|
|
with Formatz([MyOrder],[FormatID],[ParentID],[Data]) as (
|
|
Select 0,FormatID, ParentID, Data From Formats where FormatID=@FormatID
|
|
Union All
|
|
Select [MyOrder]+1,FF.FormatID, FF.ParentID, FF.Data From Formats FF
|
|
Join Formatz ZZ on ZZ.ParentID=FF.FormatID
|
|
Where ZZ.FormatID <> ZZ.ParentID)
|
|
Select Top 1 @TransFormat=v.value('./@TransFormat', 'varchar(MAX)')
|
|
FROM Formatz
|
|
CROSS APPLY Data.nodes('//TransTypes') TempXML(v)
|
|
where v.value('./@TransType', 'int')=@TranType
|
|
order by [MyOrder]
|
|
END
|
|
return @TransFormat
|
|
END |