You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
97 lines
4.6 KiB
97 lines
4.6 KiB
USE [BEA]
|
|
GO
|
|
|
|
/****** Object: UserDefinedFunction [dbo].[fnkt_get_bezeichnung] Script Date: 27.09.2013 20:11:47 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date, ,>
|
|
-- Description: <Description, ,>
|
|
-- =============================================
|
|
CREATE FUNCTION [dbo].[fnkt_get_bezeichnung]
|
|
(
|
|
@type int
|
|
|
|
)
|
|
RETURNS VARCHAR(255)
|
|
AS
|
|
BEGIN
|
|
DECLARE @result VARCHAR(255)
|
|
SELECT @result=bezeichnung FROM dbo.Beziehungsbezeichnung WHERE BeschreibungNr = @type
|
|
RETURN @result
|
|
|
|
END
|
|
|
|
GO
|
|
|
|
/****** Object: UserDefinedFunction [dbo].[fnkt_get_beziehung_sql] Script Date: 27.09.2013 20:11:47 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date, ,>
|
|
-- Description: <Description, ,>
|
|
-- =============================================
|
|
CREATE FUNCTION [dbo].[fnkt_get_beziehung_sql]
|
|
(
|
|
@table1 VARCHAR(255),
|
|
@table2 VARCHAR(255),
|
|
@beznr INT,
|
|
@keyvalue INT,
|
|
@where VARCHAR(255),
|
|
@type INT,
|
|
@personal int
|
|
)
|
|
RETURNS varchar(1024)
|
|
AS
|
|
BEGIN
|
|
DECLARE @sql VARCHAR(1024)
|
|
|
|
IF @type = 0
|
|
BEGIN
|
|
IF @personal = 1 BEGIN
|
|
SET @sql = 'SELECT dbo.' + @table1 + '_' + @table2 + '.' + @table1 + '_' + @table2 + 'Nr, dbo.personal.tgnummer' + CHAR(39) + ' '+ CHAR(39) + ' dbo.personal.name, dbo.' + @table1 + '.Sequenz, '
|
|
END ELSE BEGIN
|
|
SET @sql = 'SELECT dbo.' + @table1 + '_' + @table2 + '.' + @table1 + '_' + @table2 + 'Nr, dbo.' + @table2 + '.Bezeichnung, dbo.'+ @table1 + '.Sequenz, '
|
|
END
|
|
SET @sql = @sql + 'dbo.' + @table1 + '_' + @table2 + '.Beschreibung, dbo.' + @table1 + '_' + @table2 + '.Aktiv, dbo.' + @table1 + '_' + @table2+ '.Erstellt_am, '
|
|
SET @sql = @sql + 'dbo.' + @table1 + '_' + @table2 + '.Mutiert_am,dbo.' + @table1 + '_' + @table2 + '.Mutierer, dbo.fnkt_get_bezeichnung('+ LTRIM(RTRIM(STR(@beznr))) + ') AS Typ, '
|
|
SET @sql = @sql + 'dbo.' + @table2 + '.' + @table2 + 'Nr AS MKEY, dbo.Beziehungstyp.Bezeichnung AS Beziehungstyp '
|
|
SET @sql = @sql + 'FROM dbo.' + @table1 + '_' + @table2 + ' '
|
|
SET @sql = @sql + 'INNER JOIN dbo.' + @table1 + ' ON dbo.' + @table1 + '_' + @table2 + '.' + @table1 + 'Nr = dbo.' + @table1 + '.' + @table1+ 'Nr '
|
|
SET @sql = @sql + 'INNER JOIN dbo.' + @table2 + ' ON dbo.' + @table1 + '_' + @table2 + '.' + @table2 + 'Nr = dbo.' + @table2 + '.' + @table2+ 'Nr '
|
|
SET @sql = @sql + 'INNER JOIN dbo.Beziehungstyp ON dbo.' + @table1 + '_' + @table2 + '.Beziehungstypnr = dbo.Beziehungstyp.BeziehungstypNr '
|
|
SET @sql = @sql + 'WHERE ' + @where + ' = ' + LTRIM(RTRIM(STR(@keyvalue))) + ' and dbo.' + @table1 + '_' + @table2 + '.aktiv=1'
|
|
END
|
|
IF @type = 1
|
|
BEGIN
|
|
IF @personal = 1 BEGIN
|
|
SET @sql = 'SELECT dbo.' + @table1 + '_' + @table2 + '.' + @table1 + '_' + @table2 + 'Nr, dbo.personal.tgnummer' + CHAR(39) + ' '+ CHAR(39) + ' dbo.personal.name, dbo.' + @table1 + '.Sequenz, '
|
|
END ELSE BEGIN
|
|
SET @sql = 'SELECT dbo.' + @table1 + '_' + @table2 + '.' + @table1 + '_' + @table2 + 'Nr, dbo.' + @table2 + '.Bezeichnung, dbo.'+ @table1 + '.Sequenz, '
|
|
END
|
|
SET @sql = @sql + 'dbo.' + @table1 + '_' + @table2 + '.Beschreibung, dbo.' + @table1 + '_' + @table2 + '.Aktiv, dbo.' + @table1 + '_' + @table2+ '.Erstellt_am, '
|
|
SET @sql = @sql + 'dbo.' + @table1 + '_' + @table2 + '.Mutiert_am,dbo.' + @table1 + '_' + @table2 + '.Mutierer, dbo.fnkt_get_bezeichnung('+ LTRIM(RTRIM(STR(@beznr))) + ') AS Typ, '
|
|
SET @sql = @sql + 'dbo.' + @table2 + '.' + @table2 + 'Nr AS MKEY, dbo.Beziehungstyp.Bezeichnung AS Beziehungstyp '
|
|
SET @sql = @sql + 'FROM dbo.' + @table1 + '_' + @table2 + ' '
|
|
SET @sql = @sql + 'INNER JOIN dbo.' + @table1 + ' ON dbo.' + @table1 + '_' + @table2 + '.' + @table1 + 'Nr = dbo.' + @table1 + '.' + @table1+ 'Nr '
|
|
SET @sql = @sql + 'INNER JOIN dbo.' + @table2 + ' ON dbo.' + @table1 + '_' + @table2 + '.' + @table2 + 'Nr = dbo.' + @table2 + '.' + @table2+ 'Nr '
|
|
SET @sql = @sql + 'INNER JOIN dbo.Beziehungstyp ON dbo.' + @table1 + '_' + @table2 + '.Beziehungstypnr = dbo.Beziehungstyp.BeziehungstypNr '
|
|
SET @sql = @sql + 'WHERE ' + @where + ' = ' + LTRIM(RTRIM(STR(@keyvalue))) + ' and dbo.' + @table1 + '_' + @table2 + '.aktiv=1'
|
|
END
|
|
|
|
RETURN @sql
|
|
END
|
|
|
|
GO
|
|
|