Kplus SQL Server Funktionen

Wenn es Probleme bei der automatischen Anlage der Kplus SQL Scripts gibt, kann man sie im Management Studio mit diesem Script anlegen lassen.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Kplus Software Ges.m.b.H

-- Widmann Andreas

-- Create date: 2009-05-25

-- Description: angegebene Stellen füllen

-- Der übergebene Wert wird immer

-- auf diesen Anzahl Stellen mit

-- Vornullen aufgefüllt!

-- =============================================

CREATE FUNCTION [dbo].[KPLUS_F_Vornullen]

(

-- Add the parameters for the function here

@p_Wert int

,@p_Stellen smallint

)

RETURNS varchar(20)

AS

BEGIN

-- Declare the return variable here

DECLARE @l_Return as varchar(20),

@l_iC as int

-- Add the T-SQL statements to compute the return value here

SET @l_Return = '';

SET @l_iC = len(cast(@p_Wert as varchar(20)));

While @l_iC < @p_Stellen

BEGIN

SET @l_Return = @l_Return + '0';

SET @l_iC = @l_iC + 1;

END

If len(cast(@p_Wert as varchar(20))) > 0

SET @l_Return = @l_Return + cast(@p_Wert as varchar(20));

-- Return the result of the function

RETURN @l_Return;

END

GO

/****** Object: UserDefinedFunction [dbo].[KPLUS_F_Telefon] Script Date: 12/23/2009 15:48:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Widmann Andreas

-- Create date: 2009-05-15

-- Description: Entfernt alle nicht numerischen

-- Zeichen aus übergebenen String.

-- =============================================

CREATE FUNCTION [dbo].[KPLUS_F_Telefon]

(

-- Add the parameters for the function here

@p_sKDVorwahl AS varchar(200)

,@p_sKDTelefon AS varchar(200)

,@p_sASVorwahl AS varchar(200)

,@p_sASTelefon AS varchar(200)

)

RETURNS varchar(200)

AS

BEGIN

DECLARE @l_bIsAnsprech smallint,

@l_iLength smallint,

@l_sASTel varchar(200),

@l_sReturn varchar(200)

SET @p_sKDVorwahl = dbo.KPLUS_F_StrToNumber( @p_sKDVorwahl );

SET @p_sKDTelefon = dbo.KPLUS_F_StrToNumber( @p_sKDTelefon );

SET @p_sASVorwahl = dbo.KPLUS_F_StrToNumber( @p_sASVorwahl );

SET @p_sASTelefon = dbo.KPLUS_F_StrToNumber( @p_sASTelefon );

If len(@p_sASVorwahl) > 0 OR len(@p_sASTelefon) > 0

SET @l_bIsAnsprech = 1;

Else

SET @l_bIsAnsprech = 0;

-- Kundentelefon

If @l_bIsAnsprech = 0

BEGIN

SET @l_sReturn = @p_sKDVorwahl + @p_sKDTelefon;

END

Else

BEGIN

SET @l_iLength = len(@p_sASTelefon);

If @l_iLength <= 4 -- es ist eine Durchwahl

SET @l_sASTel = @p_sKDTelefon + @p_sASTelefon;

Else

SET @l_sASTel = @p_sASTelefon;

-- Wenn Ansprechpartner-Vorwahl belegt, dann diese verwenden, ansonsten Kundenvorwahl

If len(@p_sASVorwahl) > 0

SET @l_sASTel = @p_sASVorwahl + @l_sASTel;

Else

SET @l_sASTel = @p_sKDVorwahl + @l_sASTel;

SET @l_sReturn = @l_sASTel;

END

RETURN @l_sReturn;

END

GO

/****** Object: UserDefinedFunction [dbo].[KPLUS_F_StrToNumber] Script Date: 12/23/2009 15:49:12 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Widmann Andreas

-- Create date: 2009-05-20

-- Description: Entfernt alle nicht numerischen

-- Zeichen aus String

-- =============================================

CREATE FUNCTION [dbo].[KPLUS_F_StrToNumber]

(

-- Add the parameters for the function here

@p_sString AS varchar(200)

)

RETURNS varchar(200)

AS

BEGIN

-- Fill the table variable with the rows for your result set

-- Declare the return variable here

DECLARE @l_Length int,

@l_C int,

@l_sH char(1),

@l_Return varchar(200)

-- Add the T-SQL statements to compute the return value here

SET @l_Length = len(@p_sString);

SET @l_Return = '';

SET @l_C = 0;

While @l_C < @l_Length

BEGIN

SET @l_sH = substring( @p_sString, @l_C + 1, 1 );

If @l_sH >= '0' AND @l_sH <= '9'

SET @l_Return = @l_Return + @l_sH;

SET @l_C = @l_C + 1;

END

-- Return the result of the function

RETURN @l_Return

END

GO

/****** Object: UserDefinedFunction [dbo].[KPLUS_F_RabatteRechnen] Script Date: 12/23/2009 15:49:21 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[KPLUS_F_RabatteRechnen]

(

-- Add the parameters for the function here

@p_Vk float

,@p_Rab1 float

,@p_Rab2 float

,@p_Rab3 float

,@p_Rab4 float

,@p_Rab5 float

,@p_Fracht float

,@p_Sonder float

)

RETURNS float

AS

BEGIN

DECLARE @l_Rech float

SET @l_Rech = @p_Vk - ((@p_Vk * @p_Rab1) / 100);

SET @l_Rech = @l_Rech - ((@l_Rech * @p_Rab2) / 100);

SET @l_Rech = @l_Rech - ((@l_Rech * @p_Rab3) / 100);

SET @l_Rech = @l_Rech - ((@l_Rech * @p_Rab4) / 100);

SET @l_Rech = @l_Rech - ((@l_Rech * @p_Rab5) / 100);

SET @l_Rech = @l_Rech + @p_Fracht + @p_Sonder;

RETURN @l_Rech;

END

GO