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.
3319 lines
87 KiB
3319 lines
87 KiB
USE [Vertragsverwaltung]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_SQL_SelectAll] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select all rows from the table 'LC_SQL'
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_SQL_SelectAll]
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT all rows from the table.
|
|
SELECT
|
|
[LC_SQLNr],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv]
|
|
FROM [dbo].[LC_SQL]
|
|
ORDER BY
|
|
[LC_SQLNr] ASC
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_SQL_SelectOne] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select an existing row from the table 'LC_SQL'
|
|
-- based on the Primary Key.
|
|
-- Gets: @iLC_SQLNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_SQL_SelectOne]
|
|
@iLC_SQLNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT an existing row from the table.
|
|
SELECT
|
|
[LC_SQLNr],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv]
|
|
FROM [dbo].[LC_SQL]
|
|
WHERE
|
|
[LC_SQLNr] = @iLC_SQLNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_SQL_Delete] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will delete an existing row from the table 'LC_SQL'
|
|
-- using the Primary Key.
|
|
-- Gets: @iLC_SQLNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_SQL_Delete]
|
|
@iLC_SQLNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- DELETE an existing row from the table.
|
|
DELETE FROM [dbo].[LC_SQL]
|
|
WHERE
|
|
[LC_SQLNr] = @iLC_SQLNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_SQL_Update] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will update an existing row in the table 'LC_SQL'
|
|
-- Gets: @iLC_SQLNr int
|
|
-- Gets: @sBezeichnung varchar(50)
|
|
-- Gets: @sBeschreibung varchar(1024)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_SQL_Update]
|
|
@iLC_SQLNr int,
|
|
@sBezeichnung varchar(50),
|
|
@sBeschreibung varchar(1024),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- UPDATE an existing row in the table.
|
|
UPDATE [dbo].[LC_SQL]
|
|
SET
|
|
[Bezeichnung] = @sBezeichnung,
|
|
[Beschreibung] = @sBeschreibung,
|
|
[Erstellt_am] = @daErstellt_am,
|
|
[Mutiert_am] = @daMutiert_am,
|
|
[Mutierer] = @iMutierer,
|
|
[Aktiv] = @bAktiv
|
|
WHERE
|
|
[LC_SQLNr] = @iLC_SQLNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_SQL_Insert] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will insert 1 row in the table 'LC_SQL'
|
|
-- Gets: @iLC_SQLNr int
|
|
-- Gets: @sBezeichnung varchar(50)
|
|
-- Gets: @sBeschreibung varchar(1024)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_SQL_Insert]
|
|
@iLC_SQLNr int,
|
|
@sBezeichnung varchar(50),
|
|
@sBeschreibung varchar(1024),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- INSERT a new row in the table.
|
|
INSERT [dbo].[LC_SQL]
|
|
(
|
|
[LC_SQLNr],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv]
|
|
)
|
|
VALUES
|
|
(
|
|
@iLC_SQLNr,
|
|
@sBezeichnung,
|
|
@sBeschreibung,
|
|
@daErstellt_am,
|
|
@daMutiert_am,
|
|
@iMutierer,
|
|
@bAktiv
|
|
)
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkttyp_SelectAll] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select all rows from the table 'LC_Produkttyp'
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkttyp_SelectAll]
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT all rows from the table.
|
|
SELECT
|
|
[LC_Produkttypnr],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv]
|
|
FROM [dbo].[LC_Produkttyp]
|
|
ORDER BY
|
|
[LC_Produkttypnr] ASC
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkttyp_SelectOne] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select an existing row from the table 'LC_Produkttyp'
|
|
-- based on the Primary Key.
|
|
-- Gets: @iLC_Produkttypnr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkttyp_SelectOne]
|
|
@iLC_Produkttypnr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT an existing row from the table.
|
|
SELECT
|
|
[LC_Produkttypnr],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv]
|
|
FROM [dbo].[LC_Produkttyp]
|
|
WHERE
|
|
[LC_Produkttypnr] = @iLC_Produkttypnr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkttyp_Delete] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will delete an existing row from the table 'LC_Produkttyp'
|
|
-- using the Primary Key.
|
|
-- Gets: @iLC_Produkttypnr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkttyp_Delete]
|
|
@iLC_Produkttypnr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- DELETE an existing row from the table.
|
|
DELETE FROM [dbo].[LC_Produkttyp]
|
|
WHERE
|
|
[LC_Produkttypnr] = @iLC_Produkttypnr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkttyp_Update] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will update an existing row in the table 'LC_Produkttyp'
|
|
-- Gets: @iLC_Produkttypnr int
|
|
-- Gets: @sBezeichnung varchar(50)
|
|
-- Gets: @sBeschreibung varchar(255)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkttyp_Update]
|
|
@iLC_Produkttypnr int,
|
|
@sBezeichnung varchar(50),
|
|
@sBeschreibung varchar(255),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- UPDATE an existing row in the table.
|
|
UPDATE [dbo].[LC_Produkttyp]
|
|
SET
|
|
[Bezeichnung] = @sBezeichnung,
|
|
[Beschreibung] = @sBeschreibung,
|
|
[Erstellt_am] = @daErstellt_am,
|
|
[Mutiert_am] = @daMutiert_am,
|
|
[Mutierer] = @iMutierer,
|
|
[Aktiv] = @bAktiv
|
|
WHERE
|
|
[LC_Produkttypnr] = @iLC_Produkttypnr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkttyp_Insert] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will insert 1 row in the table 'LC_Produkttyp'
|
|
-- Gets: @iLC_Produkttypnr int
|
|
-- Gets: @sBezeichnung varchar(50)
|
|
-- Gets: @sBeschreibung varchar(255)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkttyp_Insert]
|
|
@iLC_Produkttypnr int,
|
|
@sBezeichnung varchar(50),
|
|
@sBeschreibung varchar(255),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- INSERT a new row in the table.
|
|
INSERT [dbo].[LC_Produkttyp]
|
|
(
|
|
[LC_Produkttypnr],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv]
|
|
)
|
|
VALUES
|
|
(
|
|
@iLC_Produkttypnr,
|
|
@sBezeichnung,
|
|
@sBeschreibung,
|
|
@daErstellt_am,
|
|
@daMutiert_am,
|
|
@iMutierer,
|
|
@bAktiv
|
|
)
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_lc_reporting] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[sp_lc_reporting]
|
|
@fnkt INT,
|
|
@param1 VARCHAR(4096),
|
|
@param2 VARCHAR(4096),
|
|
@param3 VARCHAR(4096),
|
|
@param4 VARCHAR(4096),
|
|
@param5 VARCHAR(4096),
|
|
@param6 VARCHAR(4096)
|
|
AS
|
|
BEGIN
|
|
-- Abgelaufene Überprüfungen
|
|
IF @fnkt=1 BEGIN
|
|
SELECT lc_produktnr AS ProduktNr, bezeichnung AS Produkt, Nächste_Ueberprüfung AS N_Ueberpruefung FROM lc_produkt WHERE Nächste_Ueberprüfung < GETDATE() AND aktiv=1 AND Nächste_Ueberprüfung<>'01.01.1900'
|
|
END
|
|
-- Abgelaufenen Produkte in N Tagen
|
|
IF @fnkt=2 BEGIN
|
|
SELECT lc_produktnr AS ProduktNr, bezeichnung AS Produkt, Nächste_Ueberprüfung AS N_Ueberpruefung, DATEDIFF(DAY, GETDATE(),Nächste_Ueberprüfung) AS in_Tagen
|
|
FROM lc_produkt
|
|
WHERE DATEDIFF(DAY,GETDATE(),Nächste_Ueberprüfung) >= @param1 AND aktiv=1 AND Nächste_Ueberprüfung<>'01.01.1900'
|
|
END
|
|
-- Abgelaufene Entscheidungen
|
|
IF @fnkt=3 BEGIN
|
|
SELECT lc_produktnr AS ProduktNr, bezeichnung AS Produkt, Entscheidungstermin AS Termin
|
|
FROM lc_produkt WHERE Entscheidungstermin < GETDATE() AND aktiv=1 AND Entscheidungstermin<>'01.01.1900'
|
|
END
|
|
--
|
|
IF @fnkt=4 BEGIN
|
|
SELECT lc_produktnr AS ProduktNr, bezeichnung AS Produkt, Entscheidungstermin AS Termin, DATEDIFF(DAY, GETDATE(),Entscheidungstermin) AS in_Tagen
|
|
FROM lc_produkt
|
|
WHERE DATEDIFF(DAY,GETDATE(),Entscheidungstermin) >= @param1 AND aktiv=1 AND Entscheidungstermin<>'01.01.1900'
|
|
END
|
|
|
|
DECLARE @cols AS NVARCHAR(MAX),
|
|
@query AS NVARCHAR(MAX)
|
|
|
|
-- Roadmap nach Phasen
|
|
IF @fnkt=5 OR @fnkt=7 BEGIN
|
|
IF @fnkt=7 BEGIN
|
|
SET @param3='31.12.2200'
|
|
SET @param2=LEFT(CONVERT(VARCHAR, DATEADD(DAY,CAST(@param2 AS int),GETDATE()), 104), 10)
|
|
PRINT @param1
|
|
PRINT @param2
|
|
PRINT @param3
|
|
PRINT @param4
|
|
END
|
|
|
|
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Phase) from dbo.lc_rpt3(@param1,@param2,@param3,@param4)
|
|
FOR XML PATH(''), TYPE
|
|
).value('.', 'NVARCHAR(MAX)')
|
|
,1,1,'')
|
|
PRINT @cols
|
|
PRINT 'aaa'
|
|
set @query = 'SELECT lc_produktnr as ProduktNr, Produkt,' + @cols + ' from
|
|
(
|
|
select p.lc_produktnr,p.Produkt,
|
|
p.datum,
|
|
d.phase
|
|
from dbo.lc_rpt4('+CHAR(39)+@param1+CHAR(39)+','+CHAR(39)+@param2+CHAR(39)+','+CHAR(39)+@param3+CHAR(39)+','+@param4+') p
|
|
left join dbo.lc_rpt3('+CHAR(39)+@param1+CHAR(39)+','+CHAR(39)+@param2+CHAR(39)+','+CHAR(39)+@param3+CHAR(39)+','+@param4+') d
|
|
on p.lc_phasenr = d.lc_phasenr
|
|
) x
|
|
pivot
|
|
(
|
|
max(datum)
|
|
for Phase in (' + @cols + ')
|
|
) p '
|
|
|
|
PRINT @query
|
|
PRINT 'bbbb'
|
|
execute(@query)
|
|
end
|
|
|
|
|
|
-- Roadmap nach Produkten
|
|
IF @fnkt=6 BEGIN
|
|
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Phase) from dbo.lc_rpt1(@param1,@param2,@param3, @param4)
|
|
FOR XML PATH(''), TYPE
|
|
).value('.', 'NVARCHAR(MAX)')
|
|
,1,1,'')
|
|
PRINT @cols
|
|
|
|
set @query = 'SELECT lc_produktnr as ProduktNr, Produkt,' + @cols + ' from
|
|
(
|
|
select p.lc_produktnr,p.Produkt,
|
|
p.datum,
|
|
d.phase
|
|
from dbo.lc_rpt2('+CHAR(39)+@param1+CHAR(39)+','+CHAR(39)+@param2+CHAR(39)+','+CHAR(39)+@param3+CHAR(39)+','+@param4+') p
|
|
left join dbo.lc_rpt1('+CHAR(39)+@param1+CHAR(39)+','+CHAR(39)+@param2+CHAR(39)+','+CHAR(39)+@param3+CHAR(39)+','+@param4+') d
|
|
on p.lc_phasenr = d.lc_phasenr
|
|
) x
|
|
pivot
|
|
(
|
|
max(datum)
|
|
for Phase in (' + @cols + ')
|
|
) p '
|
|
|
|
execute(@query)
|
|
end
|
|
|
|
|
|
|
|
-- IF @fnkt=7 BEGIN
|
|
-- select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Phase) from dbo.lc_rpt5(@param1,@param2)
|
|
-- FOR XML PATH(''), TYPE
|
|
-- ).value('.', 'NVARCHAR(MAX)')
|
|
-- ,1,1,'')
|
|
--PRINT @cols
|
|
|
|
--set @query = 'SELECT lc_produktnr as ProduktNr, Produkt,' + @cols + ' from
|
|
-- (
|
|
-- select p.lc_produktnr,p.Produkt,
|
|
-- p.datum,
|
|
-- d.phase
|
|
-- from dbo.lc_rpt6('+CHAR(39)+@param1+CHAR(39)+','+CHAR(39)+@param2+CHAR(39)+') p
|
|
-- left join dbo.lc_rpt5('+CHAR(39)+@param1+CHAR(39)+','+CHAR(39)+@param2+CHAR(39)+') d
|
|
-- on p.lc_phasenr = d.lc_phasenr
|
|
-- ) x
|
|
-- pivot
|
|
-- (
|
|
-- max(datum)
|
|
-- for Phase in (' + @cols + ')
|
|
-- ) p '
|
|
|
|
-- execute(@query)
|
|
-- end
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
--IF OBJECT_ID (N'_tmp_lc_rpt1', N'U') IS NOT NULL DROP TABLE [dbo].[_tmp_lc_rpt1]
|
|
--IF OBJECT_ID (N'_tmp_lc_rpt2', N'U') IS NOT NULL DROP TABLE [dbo].[_tmp_lc_rpt2]
|
|
--SELECT * INTO _tmp_lc_rpt1 FROM dbo.lc_rpt1(@param1,@param2,@param3)
|
|
--SELECT * INTO _tmp_lc_rpt2 FROM dbo.lc_rpt2(@param1,@param2,@param3)
|
|
|
|
|
|
--set @query = 'SELECT lc_produktnr as ProduktNr, Produkt,' + @cols + ' from
|
|
-- (
|
|
-- select p.lc_produktnr,p.Produkt,
|
|
-- p.datum,
|
|
-- d.phase
|
|
-- from _tmp_lc_rpt2 p
|
|
-- left join _tmp_lc_rpt1 d
|
|
-- on p.lc_phasenr = d.lc_phasenr
|
|
-- ) x
|
|
-- pivot
|
|
-- (
|
|
-- max(datum)
|
|
-- for Phase in (' + @cols + ')
|
|
-- ) p '
|
|
END
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Phase_SelectAll] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select all rows from the table 'LC_Phase'
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Phase_SelectAll]
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT all rows from the table.
|
|
SELECT
|
|
[LC_PhaseNr],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv]
|
|
FROM [dbo].[LC_Phase]
|
|
ORDER BY
|
|
[LC_PhaseNr] ASC
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Phase_SelectOne] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select an existing row from the table 'LC_Phase'
|
|
-- based on the Primary Key.
|
|
-- Gets: @iLC_PhaseNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Phase_SelectOne]
|
|
@iLC_PhaseNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT an existing row from the table.
|
|
SELECT
|
|
[LC_PhaseNr],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv]
|
|
FROM [dbo].[LC_Phase]
|
|
WHERE
|
|
[LC_PhaseNr] = @iLC_PhaseNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Phase_Delete] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will delete an existing row from the table 'LC_Phase'
|
|
-- using the Primary Key.
|
|
-- Gets: @iLC_PhaseNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Phase_Delete]
|
|
@iLC_PhaseNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- DELETE an existing row from the table.
|
|
DELETE FROM [dbo].[LC_Phase]
|
|
WHERE
|
|
[LC_PhaseNr] = @iLC_PhaseNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Phase_Update] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will update an existing row in the table 'LC_Phase'
|
|
-- Gets: @iLC_PhaseNr int
|
|
-- Gets: @sBezeichnung varchar(255)
|
|
-- Gets: @sBeschreibung varchar(1024)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Phase_Update]
|
|
@iLC_PhaseNr int,
|
|
@sBezeichnung varchar(255),
|
|
@sBeschreibung varchar(1024),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- UPDATE an existing row in the table.
|
|
UPDATE [dbo].[LC_Phase]
|
|
SET
|
|
[Bezeichnung] = @sBezeichnung,
|
|
[Beschreibung] = @sBeschreibung,
|
|
[Erstellt_am] = @daErstellt_am,
|
|
[Mutiert_am] = @daMutiert_am,
|
|
[Mutierer] = @iMutierer,
|
|
[Aktiv] = @bAktiv
|
|
WHERE
|
|
[LC_PhaseNr] = @iLC_PhaseNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Phase_Insert] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will insert 1 row in the table 'LC_Phase'
|
|
-- Gets: @sBezeichnung varchar(255)
|
|
-- Gets: @sBeschreibung varchar(1024)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Returns: @iLC_PhaseNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Phase_Insert]
|
|
@sBezeichnung varchar(255),
|
|
@sBeschreibung varchar(1024),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iLC_PhaseNr int OUTPUT,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- INSERT a new row in the table.
|
|
INSERT [dbo].[LC_Phase]
|
|
(
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv]
|
|
)
|
|
VALUES
|
|
(
|
|
@sBezeichnung,
|
|
@sBeschreibung,
|
|
@daErstellt_am,
|
|
@daMutiert_am,
|
|
@iMutierer,
|
|
@bAktiv
|
|
)
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
-- Get the IDENTITY value for the row just inserted.
|
|
SELECT @iLC_PhaseNr=SCOPE_IDENTITY()
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_lc_check_delete] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[sp_lc_check_delete]
|
|
@lc_produktnr INT,
|
|
@fnkt int
|
|
AS
|
|
BEGIN
|
|
IF @fnkt=1 begin
|
|
SELECT * FROM dbo.LC_Produkt WHERE Abgeloest_durch_produktnr=@lc_produktnr AND aktiv=1 ORDER BY Bezeichnung
|
|
END
|
|
IF @fnkt=2 BEGIN
|
|
SELECT * FROM dbo.LC_Produkt WHERE LC_ProduktNr_Parent=@lc_produktnr AND aktiv=1 AND IstStruktur=0 ORDER BY Bezeichnung
|
|
|
|
END
|
|
END
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_lc_get_produkte] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[sp_lc_get_produkte]
|
|
AS
|
|
BEGIN
|
|
DECLARE @tmp1 TABLE
|
|
(
|
|
id INT ,
|
|
parentid INT ,
|
|
bezeichnung VARCHAR(255) ,
|
|
imageindex INT ,
|
|
beschreibung VARCHAR(1024) ,
|
|
IstStruktur BIT ,
|
|
verweisnr INT ,
|
|
copy INT ,
|
|
originalid INT,
|
|
Produkttypnr int
|
|
)
|
|
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE lc_produkt
|
|
SET bezeichnung = ( SELECT bezeichnung
|
|
FROM applikation
|
|
WHERE verweisnr = applikation.applikationnr
|
|
)
|
|
WHERE verweisnr > 0
|
|
UPDATE lc_produkt
|
|
SET mutiert_am = GETDATE() ,
|
|
aktiv = 0
|
|
WHERE verweisnr > 0
|
|
AND verweisnr NOT IN ( SELECT applikationnr
|
|
FROM applikation )
|
|
INSERT @tmp1
|
|
SELECT lc_produktnr AS id ,
|
|
lc_produktnr_parent AS parentid ,
|
|
bezeichnung ,
|
|
imageindex AS imageindex ,
|
|
ISNULL(Beschreibung, '') AS Beschreibung ,
|
|
IstStruktur ,
|
|
ISNULL(verweisnr, 0) AS Verweisnr ,
|
|
0 AS copy ,
|
|
LC_ProduktNr,
|
|
ProdukttypNr
|
|
FROM dbo.LC_Produkt
|
|
WHERE aktiv = 1 AND IstStruktur=0
|
|
|
|
|
|
|
|
END
|
|
|
|
SELECT *
|
|
FROM @tmp1
|
|
ORDER BY bezeichnung
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_SelectAll] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select all rows from the table 'LC_Produkt'
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_SelectAll]
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT all rows from the table.
|
|
SELECT
|
|
[LC_ProduktNr],
|
|
[LC_ProduktNr_Parent],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[Imageindex],
|
|
[ProdukttypNr],
|
|
[Image],
|
|
[Version],
|
|
[StatusNr],
|
|
[Nächste_Ueberprüfung],
|
|
[Entscheidungstermin],
|
|
[Abgeloest_am],
|
|
[Abgeloest_durch],
|
|
[IstStruktur],
|
|
[Verweisnr],
|
|
[TerminBemerkung],
|
|
[Abgeloest_durch_produktnr],
|
|
[Abgeliest_durch_wen]
|
|
FROM [dbo].[LC_Produkt]
|
|
ORDER BY
|
|
[LC_ProduktNr] ASC
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_SelectOne] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select an existing row from the table 'LC_Produkt'
|
|
-- based on the Primary Key.
|
|
-- Gets: @iLC_ProduktNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_SelectOne]
|
|
@iLC_ProduktNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT an existing row from the table.
|
|
SELECT
|
|
[LC_ProduktNr],
|
|
[LC_ProduktNr_Parent],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[Imageindex],
|
|
[ProdukttypNr],
|
|
[Image],
|
|
[Version],
|
|
[StatusNr],
|
|
[Nächste_Ueberprüfung],
|
|
[Entscheidungstermin],
|
|
[Abgeloest_am],
|
|
[Abgeloest_durch],
|
|
[IstStruktur],
|
|
[Verweisnr],
|
|
[TerminBemerkung],
|
|
[Abgeloest_durch_produktnr],
|
|
[Abgeliest_durch_wen]
|
|
FROM [dbo].[LC_Produkt]
|
|
WHERE
|
|
[LC_ProduktNr] = @iLC_ProduktNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Delete] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will delete an existing row from the table 'LC_Produkt'
|
|
-- using the Primary Key.
|
|
-- Gets: @iLC_ProduktNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Delete]
|
|
@iLC_ProduktNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- DELETE an existing row from the table.
|
|
DELETE FROM [dbo].[LC_Produkt]
|
|
WHERE
|
|
[LC_ProduktNr] = @iLC_ProduktNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Update] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will update an existing row in the table 'LC_Produkt'
|
|
-- Gets: @iLC_ProduktNr int
|
|
-- Gets: @iLC_ProduktNr_Parent int
|
|
-- Gets: @sBezeichnung varchar(255)
|
|
-- Gets: @sBeschreibung varchar(1024)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Gets: @iImageindex int
|
|
-- Gets: @iProdukttypNr int
|
|
-- Gets: @blobImage image
|
|
-- Gets: @sVersion varchar(255)
|
|
-- Gets: @iStatusNr int
|
|
-- Gets: @daNächste_Ueberprüfung datetime
|
|
-- Gets: @daEntscheidungstermin datetime
|
|
-- Gets: @daAbgeloest_am datetime
|
|
-- Gets: @sAbgeloest_durch varchar(255)
|
|
-- Gets: @bIstStruktur bit
|
|
-- Gets: @iVerweisnr int
|
|
-- Gets: @sTerminBemerkung varchar(1024)
|
|
-- Gets: @iAbgeloest_durch_produktnr int
|
|
-- Gets: @iAbgeliest_durch_wen int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Update]
|
|
@iLC_ProduktNr int,
|
|
@iLC_ProduktNr_Parent int,
|
|
@sBezeichnung varchar(255),
|
|
@sBeschreibung varchar(1024),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iImageindex int,
|
|
@iProdukttypNr int,
|
|
@blobImage image,
|
|
@sVersion varchar(255),
|
|
@iStatusNr int,
|
|
@daNächste_Ueberprüfung datetime,
|
|
@daEntscheidungstermin datetime,
|
|
@daAbgeloest_am datetime,
|
|
@sAbgeloest_durch varchar(255),
|
|
@bIstStruktur bit,
|
|
@iVerweisnr int,
|
|
@sTerminBemerkung varchar(1024),
|
|
@iAbgeloest_durch_produktnr int,
|
|
@iAbgeliest_durch_wen int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- UPDATE an existing row in the table.
|
|
UPDATE [dbo].[LC_Produkt]
|
|
SET
|
|
[LC_ProduktNr_Parent] = @iLC_ProduktNr_Parent,
|
|
[Bezeichnung] = @sBezeichnung,
|
|
[Beschreibung] = @sBeschreibung,
|
|
[Erstellt_am] = @daErstellt_am,
|
|
[Mutiert_am] = @daMutiert_am,
|
|
[Mutierer] = @iMutierer,
|
|
[Aktiv] = @bAktiv,
|
|
[Imageindex] = @iImageindex,
|
|
[ProdukttypNr] = @iProdukttypNr,
|
|
[Image] = @blobImage,
|
|
[Version] = @sVersion,
|
|
[StatusNr] = @iStatusNr,
|
|
[Nächste_Ueberprüfung] = @daNächste_Ueberprüfung,
|
|
[Entscheidungstermin] = @daEntscheidungstermin,
|
|
[Abgeloest_am] = @daAbgeloest_am,
|
|
[Abgeloest_durch] = @sAbgeloest_durch,
|
|
[IstStruktur] = @bIstStruktur,
|
|
[Verweisnr] = @iVerweisnr,
|
|
[TerminBemerkung] = @sTerminBemerkung,
|
|
[Abgeloest_durch_produktnr] = @iAbgeloest_durch_produktnr,
|
|
[Abgeliest_durch_wen] = @iAbgeliest_durch_wen
|
|
WHERE
|
|
[LC_ProduktNr] = @iLC_ProduktNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Insert] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will insert 1 row in the table 'LC_Produkt'
|
|
-- Gets: @iLC_ProduktNr int
|
|
-- Gets: @iLC_ProduktNr_Parent int
|
|
-- Gets: @sBezeichnung varchar(255)
|
|
-- Gets: @sBeschreibung varchar(1024)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Gets: @iImageindex int
|
|
-- Gets: @iProdukttypNr int
|
|
-- Gets: @blobImage image
|
|
-- Gets: @sVersion varchar(255)
|
|
-- Gets: @iStatusNr int
|
|
-- Gets: @daNächste_Ueberprüfung datetime
|
|
-- Gets: @daEntscheidungstermin datetime
|
|
-- Gets: @daAbgeloest_am datetime
|
|
-- Gets: @sAbgeloest_durch varchar(255)
|
|
-- Gets: @bIstStruktur bit
|
|
-- Gets: @iVerweisnr int
|
|
-- Gets: @sTerminBemerkung varchar(1024)
|
|
-- Gets: @iAbgeloest_durch_produktnr int
|
|
-- Gets: @iAbgeliest_durch_wen int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Insert]
|
|
@iLC_ProduktNr int,
|
|
@iLC_ProduktNr_Parent int,
|
|
@sBezeichnung varchar(255),
|
|
@sBeschreibung varchar(1024),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iImageindex int,
|
|
@iProdukttypNr int,
|
|
@blobImage image,
|
|
@sVersion varchar(255),
|
|
@iStatusNr int,
|
|
@daNächste_Ueberprüfung datetime,
|
|
@daEntscheidungstermin datetime,
|
|
@daAbgeloest_am datetime,
|
|
@sAbgeloest_durch varchar(255),
|
|
@bIstStruktur bit,
|
|
@iVerweisnr int,
|
|
@sTerminBemerkung varchar(1024),
|
|
@iAbgeloest_durch_produktnr int,
|
|
@iAbgeliest_durch_wen int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- INSERT a new row in the table.
|
|
INSERT [dbo].[LC_Produkt]
|
|
(
|
|
[LC_ProduktNr],
|
|
[LC_ProduktNr_Parent],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[Imageindex],
|
|
[ProdukttypNr],
|
|
[Image],
|
|
[Version],
|
|
[StatusNr],
|
|
[Nächste_Ueberprüfung],
|
|
[Entscheidungstermin],
|
|
[Abgeloest_am],
|
|
[Abgeloest_durch],
|
|
[IstStruktur],
|
|
[Verweisnr],
|
|
[TerminBemerkung],
|
|
[Abgeloest_durch_produktnr],
|
|
[Abgeliest_durch_wen]
|
|
)
|
|
VALUES
|
|
(
|
|
@iLC_ProduktNr,
|
|
@iLC_ProduktNr_Parent,
|
|
@sBezeichnung,
|
|
@sBeschreibung,
|
|
@daErstellt_am,
|
|
@daMutiert_am,
|
|
@iMutierer,
|
|
@bAktiv,
|
|
@iImageindex,
|
|
@iProdukttypNr,
|
|
@blobImage,
|
|
@sVersion,
|
|
@iStatusNr,
|
|
@daNächste_Ueberprüfung,
|
|
@daEntscheidungstermin,
|
|
@daAbgeloest_am,
|
|
@sAbgeloest_durch,
|
|
@bIstStruktur,
|
|
@iVerweisnr,
|
|
@sTerminBemerkung,
|
|
@iAbgeloest_durch_produktnr,
|
|
@iAbgeliest_durch_wen
|
|
)
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Copy_SelectAll] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select all rows from the table 'LC_Produkt_Copy'
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Copy_SelectAll]
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT all rows from the table.
|
|
SELECT
|
|
[lc_produkt_copynr],
|
|
[lc_produkt_parentnr],
|
|
[lc_produktnr],
|
|
[erstellt_am],
|
|
[mutiert_am],
|
|
[mutierer],
|
|
[aktiv]
|
|
FROM [dbo].[LC_Produkt_Copy]
|
|
ORDER BY
|
|
[lc_produkt_copynr] ASC
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Copy_SelectOne] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select an existing row from the table 'LC_Produkt_Copy'
|
|
-- based on the Primary Key.
|
|
-- Gets: @ilc_produkt_copynr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Copy_SelectOne]
|
|
@ilc_produkt_copynr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT an existing row from the table.
|
|
SELECT
|
|
[lc_produkt_copynr],
|
|
[lc_produkt_parentnr],
|
|
[lc_produktnr],
|
|
[erstellt_am],
|
|
[mutiert_am],
|
|
[mutierer],
|
|
[aktiv]
|
|
FROM [dbo].[LC_Produkt_Copy]
|
|
WHERE
|
|
[lc_produkt_copynr] = @ilc_produkt_copynr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Copy_Delete] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will delete an existing row from the table 'LC_Produkt_Copy'
|
|
-- using the Primary Key.
|
|
-- Gets: @ilc_produkt_copynr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Copy_Delete]
|
|
@ilc_produkt_copynr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- DELETE an existing row from the table.
|
|
DELETE FROM [dbo].[LC_Produkt_Copy]
|
|
WHERE
|
|
[lc_produkt_copynr] = @ilc_produkt_copynr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Copy_Update] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will update an existing row in the table 'LC_Produkt_Copy'
|
|
-- Gets: @ilc_produkt_copynr int
|
|
-- Gets: @ilc_produkt_parentnr int
|
|
-- Gets: @ilc_produktnr int
|
|
-- Gets: @daerstellt_am datetime
|
|
-- Gets: @damutiert_am datetime
|
|
-- Gets: @imutierer int
|
|
-- Gets: @baktiv bit
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Copy_Update]
|
|
@ilc_produkt_copynr int,
|
|
@ilc_produkt_parentnr int,
|
|
@ilc_produktnr int,
|
|
@daerstellt_am datetime,
|
|
@damutiert_am datetime,
|
|
@imutierer int,
|
|
@baktiv bit,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- UPDATE an existing row in the table.
|
|
UPDATE [dbo].[LC_Produkt_Copy]
|
|
SET
|
|
[lc_produkt_parentnr] = @ilc_produkt_parentnr,
|
|
[lc_produktnr] = @ilc_produktnr,
|
|
[erstellt_am] = @daerstellt_am,
|
|
[mutiert_am] = @damutiert_am,
|
|
[mutierer] = @imutierer,
|
|
[aktiv] = @baktiv
|
|
WHERE
|
|
[lc_produkt_copynr] = @ilc_produkt_copynr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Copy_Insert] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will insert 1 row in the table 'LC_Produkt_Copy'
|
|
-- Gets: @ilc_produkt_copynr int
|
|
-- Gets: @ilc_produkt_parentnr int
|
|
-- Gets: @ilc_produktnr int
|
|
-- Gets: @daerstellt_am datetime
|
|
-- Gets: @damutiert_am datetime
|
|
-- Gets: @imutierer int
|
|
-- Gets: @baktiv bit
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Copy_Insert]
|
|
@ilc_produkt_copynr int,
|
|
@ilc_produkt_parentnr int,
|
|
@ilc_produktnr int,
|
|
@daerstellt_am datetime,
|
|
@damutiert_am datetime,
|
|
@imutierer int,
|
|
@baktiv bit,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- INSERT a new row in the table.
|
|
INSERT [dbo].[LC_Produkt_Copy]
|
|
(
|
|
[lc_produkt_copynr],
|
|
[lc_produkt_parentnr],
|
|
[lc_produktnr],
|
|
[erstellt_am],
|
|
[mutiert_am],
|
|
[mutierer],
|
|
[aktiv]
|
|
)
|
|
VALUES
|
|
(
|
|
@ilc_produkt_copynr,
|
|
@ilc_produkt_parentnr,
|
|
@ilc_produktnr,
|
|
@daerstellt_am,
|
|
@damutiert_am,
|
|
@imutierer,
|
|
@baktiv
|
|
)
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_lc_get_Struktur] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[sp_lc_get_Struktur]
|
|
AS
|
|
BEGIN
|
|
declare @tmp1 TABLE (id int, parentid int, bezeichnung varchar(255),imageindex INT,beschreibung VARCHAR(1024), copy int)
|
|
|
|
SET NOCOUNT ON;
|
|
|
|
INSERT @tmp1 SELECT lc_produktnr AS id, lc_produktnr_parent AS parentid, bezeichnung, imageindex AS imageindex, ISNULL(Beschreibung,'') AS Beschreibung,0 FROM dbo.LC_Produkt WHERE aktiv=1 AND IstStruktur=1
|
|
|
|
INSERT @tmp1 ( id, parentid, bezeichnung, imageindex,beschreibung,copy)
|
|
VALUES ( 0, NULL, 'Root', 1,'',0)
|
|
|
|
|
|
UPDATE @tmp1 SET imageindex = imageindex + 0 WHERE id IN (SELECT lc_produktnr FROM lc_produkt WHERE IstStruktur=1)
|
|
SELECT *
|
|
FROM @tmp1
|
|
ORDER BY bezeichnung
|
|
|
|
|
|
END
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_LC_get_stammdaten] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER OFF
|
|
GO
|
|
|
|
|
|
|
|
-- =============================================
|
|
-- Author: Stefan Hutter
|
|
-- Create date: 14.4.2009
|
|
-- Description: Liest die Stammdatentabellen
|
|
-- Changelog:
|
|
-- 15.4.09 Anpassung, dass nur die aktiven Datenelemente ausgelesen werden
|
|
-- =============================================
|
|
Create PROCEDURE [dbo].[sp_LC_get_stammdaten]
|
|
@mitarbeiternr int,
|
|
@tabelle varchar(255),
|
|
@orderby varchar(255)
|
|
as
|
|
begin
|
|
declare @xsql varchar(255)
|
|
if upper(@tabelle)='KOSTENART' begin
|
|
select kostenartnr, bezeichnung + ' - ' + beschreibung as Bezeichnung from kostenart where aktiv=1 order by bezeichnung
|
|
return
|
|
end
|
|
|
|
if upper(@tabelle)='KONTAKTTYP_GREMIUM' begin
|
|
select kontakttypnr, bezeichnung, applikationsbereichnr from kontakttyp where applikationsbereichnr=3 and aktiv=1 order by bezeichnung
|
|
return
|
|
end
|
|
|
|
if upper(@tabelle)='RELEASEART' begin
|
|
select Releaseartnr, bezeichnung as Bezeichnung from Releaseart where aktiv=1 order by bezeichnung
|
|
return
|
|
end
|
|
|
|
if upper(@tabelle)='PERSON' begin
|
|
select personnr, Name +' '+Vorname as Bezeichnung from Person where Vertragspartnernr=1 and Aktiv=1 order by Name, Vorname
|
|
return
|
|
end
|
|
if upper(@tabelle)='PERSON1' begin
|
|
select personnr, Name +' '+Vorname as Bezeichnung from Person where Vertragspartnernr=1 and Aktiv=1 order by Name, Vorname
|
|
return
|
|
end
|
|
if upper(@tabelle)='PERSON2' begin
|
|
select personnr, Name +' '+Vorname as Bezeichnung from Person where Vertragspartnernr=1 and Aktiv=1 order by Name, Vorname
|
|
return
|
|
end
|
|
|
|
if upper(@tabelle)='INSTALLATIONTYP' begin
|
|
SELECT * from installationtyp ORDER BY sort
|
|
return
|
|
end
|
|
|
|
|
|
set @xsql='Select * from ' + @tabelle + ' where aktiv=1 order by ' + @orderby
|
|
exec(@xsql)
|
|
end
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_get_LCdokumente] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
Create PROCEDURE [dbo].[sp_get_LCdokumente]
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select all rows from the table 'Kuendigungsfrist'
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
@keyvalue int,
|
|
@doktype int,
|
|
@mitarbeiternr int
|
|
AS
|
|
SELECT 0 as DokIcon, dbo.lc_Dokument.DokumentNr, dbo.lc_Dokument.KeyValue, dbo.lc_Dokumenttyp.Bezeichnung as Dokumenttyp, dbo.lc_Dokument.Bezeichnung AS Bezeichnung, dbo.lc_Dokument.Beschreibung,
|
|
dbo.lc_Dokument.Filename, dbo.lc_Dokument.OriginalFilename_incl_Path, dbo.lc_Dokument.Version, dbo.lc_Dokument.VersionsNr, dbo.lc_Dokument.Versionsdatum,
|
|
dbo.lc_Dokument.Erstellt_am, dbo.lc_Dokument.Mutiert_am, dbo.lc_Dokument.Mutierer, dbo.lc_Dokument.Aktiv, dbo.lc_Dokument.DocImage, 0 as DokIdon
|
|
FROM dbo.lc_Dokument INNER JOIN
|
|
dbo.lc_Dokumenttyp ON dbo.lc_Dokument.DokumenttypNr = dbo.lc_Dokumenttyp.Dokumenttypnr
|
|
where Keyvalue = @keyvalue and doktype=@doktype
|
|
order by dbo.lc_dokument.erstellt_am desc
|
|
|
|
|
|
|
|
RETURN
|
|
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Dokumenttyp_SelectAll] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select all rows from the table 'LC_Dokumenttyp'
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Dokumenttyp_SelectAll]
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT all rows from the table.
|
|
SELECT
|
|
[Dokumenttypnr],
|
|
[Bezeichnung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv]
|
|
FROM [dbo].[LC_Dokumenttyp]
|
|
ORDER BY
|
|
[Dokumenttypnr] ASC
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Dokumenttyp_SelectOne] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select an existing row from the table 'LC_Dokumenttyp'
|
|
-- based on the Primary Key.
|
|
-- Gets: @iDokumenttypnr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Dokumenttyp_SelectOne]
|
|
@iDokumenttypnr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT an existing row from the table.
|
|
SELECT
|
|
[Dokumenttypnr],
|
|
[Bezeichnung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv]
|
|
FROM [dbo].[LC_Dokumenttyp]
|
|
WHERE
|
|
[Dokumenttypnr] = @iDokumenttypnr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Dokumenttyp_Delete] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will delete an existing row from the table 'LC_Dokumenttyp'
|
|
-- using the Primary Key.
|
|
-- Gets: @iDokumenttypnr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Dokumenttyp_Delete]
|
|
@iDokumenttypnr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- DELETE an existing row from the table.
|
|
DELETE FROM [dbo].[LC_Dokumenttyp]
|
|
WHERE
|
|
[Dokumenttypnr] = @iDokumenttypnr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Dokumenttyp_Update] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will update an existing row in the table 'LC_Dokumenttyp'
|
|
-- Gets: @iDokumenttypnr int
|
|
-- Gets: @sBezeichnung varchar(255)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Dokumenttyp_Update]
|
|
@iDokumenttypnr int,
|
|
@sBezeichnung varchar(255),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- UPDATE an existing row in the table.
|
|
UPDATE [dbo].[LC_Dokumenttyp]
|
|
SET
|
|
[Bezeichnung] = @sBezeichnung,
|
|
[Erstellt_am] = @daErstellt_am,
|
|
[Mutiert_am] = @daMutiert_am,
|
|
[Mutierer] = @iMutierer,
|
|
[Aktiv] = @bAktiv
|
|
WHERE
|
|
[Dokumenttypnr] = @iDokumenttypnr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Dokumenttyp_Insert] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will insert 1 row in the table 'LC_Dokumenttyp'
|
|
-- Gets: @iDokumenttypnr int
|
|
-- Gets: @sBezeichnung varchar(255)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Dokumenttyp_Insert]
|
|
@iDokumenttypnr int,
|
|
@sBezeichnung varchar(255),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- INSERT a new row in the table.
|
|
INSERT [dbo].[LC_Dokumenttyp]
|
|
(
|
|
[Dokumenttypnr],
|
|
[Bezeichnung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv]
|
|
)
|
|
VALUES
|
|
(
|
|
@iDokumenttypnr,
|
|
@sBezeichnung,
|
|
@daErstellt_am,
|
|
@daMutiert_am,
|
|
@iMutierer,
|
|
@bAktiv
|
|
)
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_DokumentAblageTyp_SelectAll] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select all rows from the table 'LC_DokumentAblageTyp'
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_DokumentAblageTyp_SelectAll]
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT all rows from the table.
|
|
SELECT
|
|
[DokumentAblageTypNr],
|
|
[Bezeichnung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[MandantNr]
|
|
FROM [dbo].[LC_DokumentAblageTyp]
|
|
ORDER BY
|
|
[DokumentAblageTypNr] ASC
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_DokumentAblageTyp_SelectOne] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select an existing row from the table 'LC_DokumentAblageTyp'
|
|
-- based on the Primary Key.
|
|
-- Gets: @iDokumentAblageTypNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_DokumentAblageTyp_SelectOne]
|
|
@iDokumentAblageTypNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT an existing row from the table.
|
|
SELECT
|
|
[DokumentAblageTypNr],
|
|
[Bezeichnung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[MandantNr]
|
|
FROM [dbo].[LC_DokumentAblageTyp]
|
|
WHERE
|
|
[DokumentAblageTypNr] = @iDokumentAblageTypNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_DokumentAblageTyp_Delete] Script Date: 02.05.2015 09:42:45 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will delete an existing row from the table 'LC_DokumentAblageTyp'
|
|
-- using the Primary Key.
|
|
-- Gets: @iDokumentAblageTypNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_DokumentAblageTyp_Delete]
|
|
@iDokumentAblageTypNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- DELETE an existing row from the table.
|
|
DELETE FROM [dbo].[LC_DokumentAblageTyp]
|
|
WHERE
|
|
[DokumentAblageTypNr] = @iDokumentAblageTypNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_DokumentAblageTyp_Update] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will update an existing row in the table 'LC_DokumentAblageTyp'
|
|
-- Gets: @iDokumentAblageTypNr int
|
|
-- Gets: @sBezeichnung varchar(255)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Gets: @iMandantNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_DokumentAblageTyp_Update]
|
|
@iDokumentAblageTypNr int,
|
|
@sBezeichnung varchar(255),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iMandantNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- UPDATE an existing row in the table.
|
|
UPDATE [dbo].[LC_DokumentAblageTyp]
|
|
SET
|
|
[Bezeichnung] = @sBezeichnung,
|
|
[Erstellt_am] = @daErstellt_am,
|
|
[Mutiert_am] = @daMutiert_am,
|
|
[Mutierer] = @iMutierer,
|
|
[Aktiv] = @bAktiv,
|
|
[MandantNr] = @iMandantNr
|
|
WHERE
|
|
[DokumentAblageTypNr] = @iDokumentAblageTypNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_DokumentAblageTyp_Insert] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will insert 1 row in the table 'LC_DokumentAblageTyp'
|
|
-- Gets: @iDokumentAblageTypNr int
|
|
-- Gets: @sBezeichnung varchar(255)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Gets: @iMandantNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_DokumentAblageTyp_Insert]
|
|
@iDokumentAblageTypNr int,
|
|
@sBezeichnung varchar(255),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iMandantNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- INSERT a new row in the table.
|
|
INSERT [dbo].[LC_DokumentAblageTyp]
|
|
(
|
|
[DokumentAblageTypNr],
|
|
[Bezeichnung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[MandantNr]
|
|
)
|
|
VALUES
|
|
(
|
|
@iDokumentAblageTypNr,
|
|
@sBezeichnung,
|
|
@daErstellt_am,
|
|
@daMutiert_am,
|
|
@iMutierer,
|
|
@bAktiv,
|
|
@iMandantNr
|
|
)
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_DokumentAblageort_SelectAll] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select all rows from the table 'LC_DokumentAblageort'
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_DokumentAblageort_SelectAll]
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT all rows from the table.
|
|
SELECT
|
|
[DokumentablageortNr],
|
|
[DokumentablagetypNr],
|
|
[DokumentNr],
|
|
[Ablageort],
|
|
[Beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[MandantNr]
|
|
FROM [dbo].[LC_DokumentAblageort]
|
|
ORDER BY
|
|
[DokumentablageortNr] ASC
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_DokumentAblageort_SelectOne] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select an existing row from the table 'LC_DokumentAblageort'
|
|
-- based on the Primary Key.
|
|
-- Gets: @iDokumentablageortNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_DokumentAblageort_SelectOne]
|
|
@iDokumentablageortNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT an existing row from the table.
|
|
SELECT
|
|
[DokumentablageortNr],
|
|
[DokumentablagetypNr],
|
|
[DokumentNr],
|
|
[Ablageort],
|
|
[Beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[MandantNr]
|
|
FROM [dbo].[LC_DokumentAblageort]
|
|
WHERE
|
|
[DokumentablageortNr] = @iDokumentablageortNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_DokumentAblageort_Delete] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will delete an existing row from the table 'LC_DokumentAblageort'
|
|
-- using the Primary Key.
|
|
-- Gets: @iDokumentablageortNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_DokumentAblageort_Delete]
|
|
@iDokumentablageortNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- DELETE an existing row from the table.
|
|
DELETE FROM [dbo].[LC_DokumentAblageort]
|
|
WHERE
|
|
[DokumentablageortNr] = @iDokumentablageortNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_DokumentAblageort_Update] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will update an existing row in the table 'LC_DokumentAblageort'
|
|
-- Gets: @iDokumentablageortNr int
|
|
-- Gets: @iDokumentablagetypNr int
|
|
-- Gets: @iDokumentNr int
|
|
-- Gets: @sAblageort varchar(255)
|
|
-- Gets: @sBeschreibung varchar(255)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Gets: @iMandantNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_DokumentAblageort_Update]
|
|
@iDokumentablageortNr int,
|
|
@iDokumentablagetypNr int,
|
|
@iDokumentNr int,
|
|
@sAblageort varchar(255),
|
|
@sBeschreibung varchar(255),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iMandantNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- UPDATE an existing row in the table.
|
|
UPDATE [dbo].[LC_DokumentAblageort]
|
|
SET
|
|
[DokumentablagetypNr] = @iDokumentablagetypNr,
|
|
[DokumentNr] = @iDokumentNr,
|
|
[Ablageort] = @sAblageort,
|
|
[Beschreibung] = @sBeschreibung,
|
|
[Erstellt_am] = @daErstellt_am,
|
|
[Mutiert_am] = @daMutiert_am,
|
|
[Mutierer] = @iMutierer,
|
|
[Aktiv] = @bAktiv,
|
|
[MandantNr] = @iMandantNr
|
|
WHERE
|
|
[DokumentablageortNr] = @iDokumentablageortNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_DokumentAblageort_Insert] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will insert 1 row in the table 'LC_DokumentAblageort'
|
|
-- Gets: @iDokumentablageortNr int
|
|
-- Gets: @iDokumentablagetypNr int
|
|
-- Gets: @iDokumentNr int
|
|
-- Gets: @sAblageort varchar(255)
|
|
-- Gets: @sBeschreibung varchar(255)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Gets: @iMandantNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_DokumentAblageort_Insert]
|
|
@iDokumentablageortNr int,
|
|
@iDokumentablagetypNr int,
|
|
@iDokumentNr int,
|
|
@sAblageort varchar(255),
|
|
@sBeschreibung varchar(255),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iMandantNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- INSERT a new row in the table.
|
|
INSERT [dbo].[LC_DokumentAblageort]
|
|
(
|
|
[DokumentablageortNr],
|
|
[DokumentablagetypNr],
|
|
[DokumentNr],
|
|
[Ablageort],
|
|
[Beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[MandantNr]
|
|
)
|
|
VALUES
|
|
(
|
|
@iDokumentablageortNr,
|
|
@iDokumentablagetypNr,
|
|
@iDokumentNr,
|
|
@sAblageort,
|
|
@sBeschreibung,
|
|
@daErstellt_am,
|
|
@daMutiert_am,
|
|
@iMutierer,
|
|
@bAktiv,
|
|
@iMandantNr
|
|
)
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Dokument_SelectAll] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select all rows from the table 'LC_Dokument'
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Dokument_SelectAll]
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT all rows from the table.
|
|
SELECT
|
|
[DokumentNr],
|
|
[KeyValue],
|
|
[DokType],
|
|
[DokumenttypNr],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Filename],
|
|
[OriginalFilename_incl_Path],
|
|
[Version],
|
|
[VersionsNr],
|
|
[Versionsdatum],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[SecurityLevelNr],
|
|
[DocImage]
|
|
FROM [dbo].[LC_Dokument]
|
|
ORDER BY
|
|
[DokumentNr] ASC
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Dokument_SelectOne] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select an existing row from the table 'LC_Dokument'
|
|
-- based on the Primary Key.
|
|
-- Gets: @iDokumentNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Dokument_SelectOne]
|
|
@iDokumentNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT an existing row from the table.
|
|
SELECT
|
|
[DokumentNr],
|
|
[KeyValue],
|
|
[DokType],
|
|
[DokumenttypNr],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Filename],
|
|
[OriginalFilename_incl_Path],
|
|
[Version],
|
|
[VersionsNr],
|
|
[Versionsdatum],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[SecurityLevelNr],
|
|
[DocImage]
|
|
FROM [dbo].[LC_Dokument]
|
|
WHERE
|
|
[DokumentNr] = @iDokumentNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Dokument_Delete] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will delete an existing row from the table 'LC_Dokument'
|
|
-- using the Primary Key.
|
|
-- Gets: @iDokumentNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Dokument_Delete]
|
|
@iDokumentNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- DELETE an existing row from the table.
|
|
DELETE FROM [dbo].[LC_Dokument]
|
|
WHERE
|
|
[DokumentNr] = @iDokumentNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Dokument_Update] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will update an existing row in the table 'LC_Dokument'
|
|
-- Gets: @iDokumentNr int
|
|
-- Gets: @iKeyValue int
|
|
-- Gets: @iDokType int
|
|
-- Gets: @iDokumenttypNr int
|
|
-- Gets: @sBezeichnung varchar(50)
|
|
-- Gets: @sBeschreibung varchar(2048)
|
|
-- Gets: @sFilename varchar(255)
|
|
-- Gets: @sOriginalFilename_incl_Path varchar(255)
|
|
-- Gets: @sVersion varchar(50)
|
|
-- Gets: @sVersionsNr varchar(50)
|
|
-- Gets: @daVersionsdatum datetime
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Gets: @iSecurityLevelNr int
|
|
-- Gets: @blobDocImage image
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Dokument_Update]
|
|
@iDokumentNr int,
|
|
@iKeyValue int,
|
|
@iDokType int,
|
|
@iDokumenttypNr int,
|
|
@sBezeichnung varchar(50),
|
|
@sBeschreibung varchar(2048),
|
|
@sFilename varchar(255),
|
|
@sOriginalFilename_incl_Path varchar(255),
|
|
@sVersion varchar(50),
|
|
@sVersionsNr varchar(50),
|
|
@daVersionsdatum datetime,
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iSecurityLevelNr int,
|
|
@blobDocImage image,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- UPDATE an existing row in the table.
|
|
UPDATE [dbo].[LC_Dokument]
|
|
SET
|
|
[KeyValue] = @iKeyValue,
|
|
[DokType] = @iDokType,
|
|
[DokumenttypNr] = @iDokumenttypNr,
|
|
[Bezeichnung] = @sBezeichnung,
|
|
[Beschreibung] = @sBeschreibung,
|
|
[Filename] = @sFilename,
|
|
[OriginalFilename_incl_Path] = @sOriginalFilename_incl_Path,
|
|
[Version] = @sVersion,
|
|
[VersionsNr] = @sVersionsNr,
|
|
[Versionsdatum] = @daVersionsdatum,
|
|
[Erstellt_am] = @daErstellt_am,
|
|
[Mutiert_am] = @daMutiert_am,
|
|
[Mutierer] = @iMutierer,
|
|
[Aktiv] = @bAktiv,
|
|
[SecurityLevelNr] = @iSecurityLevelNr,
|
|
[DocImage] = @blobDocImage
|
|
WHERE
|
|
[DokumentNr] = @iDokumentNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Dokument_Insert] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will insert 1 row in the table 'LC_Dokument'
|
|
-- Gets: @iDokumentNr int
|
|
-- Gets: @iKeyValue int
|
|
-- Gets: @iDokType int
|
|
-- Gets: @iDokumenttypNr int
|
|
-- Gets: @sBezeichnung varchar(50)
|
|
-- Gets: @sBeschreibung varchar(2048)
|
|
-- Gets: @sFilename varchar(255)
|
|
-- Gets: @sOriginalFilename_incl_Path varchar(255)
|
|
-- Gets: @sVersion varchar(50)
|
|
-- Gets: @sVersionsNr varchar(50)
|
|
-- Gets: @daVersionsdatum datetime
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Gets: @iSecurityLevelNr int
|
|
-- Gets: @blobDocImage image
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Dokument_Insert]
|
|
@iDokumentNr int,
|
|
@iKeyValue int,
|
|
@iDokType int,
|
|
@iDokumenttypNr int,
|
|
@sBezeichnung varchar(50),
|
|
@sBeschreibung varchar(2048),
|
|
@sFilename varchar(255),
|
|
@sOriginalFilename_incl_Path varchar(255),
|
|
@sVersion varchar(50),
|
|
@sVersionsNr varchar(50),
|
|
@daVersionsdatum datetime,
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iSecurityLevelNr int,
|
|
@blobDocImage image,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- INSERT a new row in the table.
|
|
INSERT [dbo].[LC_Dokument]
|
|
(
|
|
[DokumentNr],
|
|
[KeyValue],
|
|
[DokType],
|
|
[DokumenttypNr],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Filename],
|
|
[OriginalFilename_incl_Path],
|
|
[Version],
|
|
[VersionsNr],
|
|
[Versionsdatum],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[SecurityLevelNr],
|
|
[DocImage]
|
|
)
|
|
VALUES
|
|
(
|
|
@iDokumentNr,
|
|
@iKeyValue,
|
|
@iDokType,
|
|
@iDokumenttypNr,
|
|
@sBezeichnung,
|
|
@sBeschreibung,
|
|
@sFilename,
|
|
@sOriginalFilename_incl_Path,
|
|
@sVersion,
|
|
@sVersionsNr,
|
|
@daVersionsdatum,
|
|
@daErstellt_am,
|
|
@daMutiert_am,
|
|
@iMutierer,
|
|
@bAktiv,
|
|
@iSecurityLevelNr,
|
|
@blobDocImage
|
|
)
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Phase_SelectAll] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select all rows from the table 'LC_Produkt_Phase'
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Phase_SelectAll]
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT all rows from the table.
|
|
SELECT
|
|
[LC_Produkt_PhaseNr],
|
|
[LC_ProduktNr],
|
|
[LC_PhaseNr],
|
|
[Datum],
|
|
[Bemerkung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv]
|
|
FROM [dbo].[LC_Produkt_Phase]
|
|
ORDER BY
|
|
[LC_Produkt_PhaseNr] ASC
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Phase_SelectOne] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select an existing row from the table 'LC_Produkt_Phase'
|
|
-- based on the Primary Key.
|
|
-- Gets: @iLC_Produkt_PhaseNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Phase_SelectOne]
|
|
@iLC_Produkt_PhaseNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT an existing row from the table.
|
|
SELECT
|
|
[LC_Produkt_PhaseNr],
|
|
[LC_ProduktNr],
|
|
[LC_PhaseNr],
|
|
[Datum],
|
|
[Bemerkung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv]
|
|
FROM [dbo].[LC_Produkt_Phase]
|
|
WHERE
|
|
[LC_Produkt_PhaseNr] = @iLC_Produkt_PhaseNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Phase_Delete] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will delete an existing row from the table 'LC_Produkt_Phase'
|
|
-- using the Primary Key.
|
|
-- Gets: @iLC_Produkt_PhaseNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Phase_Delete]
|
|
@iLC_Produkt_PhaseNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- DELETE an existing row from the table.
|
|
DELETE FROM [dbo].[LC_Produkt_Phase]
|
|
WHERE
|
|
[LC_Produkt_PhaseNr] = @iLC_Produkt_PhaseNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Phase_Update] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will update an existing row in the table 'LC_Produkt_Phase'
|
|
-- Gets: @iLC_Produkt_PhaseNr int
|
|
-- Gets: @iLC_ProduktNr int
|
|
-- Gets: @iLC_PhaseNr int
|
|
-- Gets: @daDatum datetime
|
|
-- Gets: @sBemerkung varchar(255)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Phase_Update]
|
|
@iLC_Produkt_PhaseNr int,
|
|
@iLC_ProduktNr int,
|
|
@iLC_PhaseNr int,
|
|
@daDatum datetime,
|
|
@sBemerkung varchar(255),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- UPDATE an existing row in the table.
|
|
UPDATE [dbo].[LC_Produkt_Phase]
|
|
SET
|
|
[LC_ProduktNr] = @iLC_ProduktNr,
|
|
[LC_PhaseNr] = @iLC_PhaseNr,
|
|
[Datum] = @daDatum,
|
|
[Bemerkung] = @sBemerkung,
|
|
[Erstellt_am] = @daErstellt_am,
|
|
[Mutiert_am] = @daMutiert_am,
|
|
[Mutierer] = @iMutierer,
|
|
[Aktiv] = @bAktiv
|
|
WHERE
|
|
[LC_Produkt_PhaseNr] = @iLC_Produkt_PhaseNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Phase_Insert] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will insert 1 row in the table 'LC_Produkt_Phase'
|
|
-- Gets: @iLC_ProduktNr int
|
|
-- Gets: @iLC_PhaseNr int
|
|
-- Gets: @daDatum datetime
|
|
-- Gets: @sBemerkung varchar(255)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Returns: @iLC_Produkt_PhaseNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Phase_Insert]
|
|
@iLC_ProduktNr int,
|
|
@iLC_PhaseNr int,
|
|
@daDatum datetime,
|
|
@sBemerkung varchar(255),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iLC_Produkt_PhaseNr int OUTPUT,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- INSERT a new row in the table.
|
|
INSERT [dbo].[LC_Produkt_Phase]
|
|
(
|
|
[LC_ProduktNr],
|
|
[LC_PhaseNr],
|
|
[Datum],
|
|
[Bemerkung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv]
|
|
)
|
|
VALUES
|
|
(
|
|
@iLC_ProduktNr,
|
|
@iLC_PhaseNr,
|
|
@daDatum,
|
|
@sBemerkung,
|
|
@daErstellt_am,
|
|
@daMutiert_am,
|
|
@iMutierer,
|
|
@bAktiv
|
|
)
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
-- Get the IDENTITY value for the row just inserted.
|
|
SELECT @iLC_Produkt_PhaseNr=SCOPE_IDENTITY()
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Dokument_SelectAll] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select all rows from the table 'LC_Produkt_Dokument'
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Dokument_SelectAll]
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT all rows from the table.
|
|
SELECT
|
|
[LC_Produkt_DokumentNr],
|
|
[LC_ProduktNr],
|
|
[Bezeichnung],
|
|
[beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[Dokument]
|
|
FROM [dbo].[LC_Produkt_Dokument]
|
|
ORDER BY
|
|
[LC_Produkt_DokumentNr] ASC
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Dokument_SelectOne] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select an existing row from the table 'LC_Produkt_Dokument'
|
|
-- based on the Primary Key.
|
|
-- Gets: @iLC_Produkt_DokumentNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Dokument_SelectOne]
|
|
@iLC_Produkt_DokumentNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT an existing row from the table.
|
|
SELECT
|
|
[LC_Produkt_DokumentNr],
|
|
[LC_ProduktNr],
|
|
[Bezeichnung],
|
|
[beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[Dokument]
|
|
FROM [dbo].[LC_Produkt_Dokument]
|
|
WHERE
|
|
[LC_Produkt_DokumentNr] = @iLC_Produkt_DokumentNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Dokument_Delete] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will delete an existing row from the table 'LC_Produkt_Dokument'
|
|
-- using the Primary Key.
|
|
-- Gets: @iLC_Produkt_DokumentNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Dokument_Delete]
|
|
@iLC_Produkt_DokumentNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- DELETE an existing row from the table.
|
|
DELETE FROM [dbo].[LC_Produkt_Dokument]
|
|
WHERE
|
|
[LC_Produkt_DokumentNr] = @iLC_Produkt_DokumentNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Dokument_Update] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will update an existing row in the table 'LC_Produkt_Dokument'
|
|
-- Gets: @iLC_Produkt_DokumentNr int
|
|
-- Gets: @iLC_ProduktNr int
|
|
-- Gets: @sBezeichnung varchar(50)
|
|
-- Gets: @sbeschreibung varchar(255)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Gets: @blobDokument image
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Dokument_Update]
|
|
@iLC_Produkt_DokumentNr int,
|
|
@iLC_ProduktNr int,
|
|
@sBezeichnung varchar(50),
|
|
@sbeschreibung varchar(255),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@blobDokument image,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- UPDATE an existing row in the table.
|
|
UPDATE [dbo].[LC_Produkt_Dokument]
|
|
SET
|
|
[LC_ProduktNr] = @iLC_ProduktNr,
|
|
[Bezeichnung] = @sBezeichnung,
|
|
[beschreibung] = @sbeschreibung,
|
|
[Erstellt_am] = @daErstellt_am,
|
|
[Mutiert_am] = @daMutiert_am,
|
|
[Mutierer] = @iMutierer,
|
|
[Aktiv] = @bAktiv,
|
|
[Dokument] = @blobDokument
|
|
WHERE
|
|
[LC_Produkt_DokumentNr] = @iLC_Produkt_DokumentNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Produkt_Dokument_Insert] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will insert 1 row in the table 'LC_Produkt_Dokument'
|
|
-- Gets: @iLC_ProduktNr int
|
|
-- Gets: @sBezeichnung varchar(50)
|
|
-- Gets: @sbeschreibung varchar(255)
|
|
-- Gets: @daErstellt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Gets: @blobDokument image
|
|
-- Returns: @iLC_Produkt_DokumentNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Dokument_Insert]
|
|
@iLC_ProduktNr int,
|
|
@sBezeichnung varchar(50),
|
|
@sbeschreibung varchar(255),
|
|
@daErstellt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@blobDokument image,
|
|
@iLC_Produkt_DokumentNr int OUTPUT,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- INSERT a new row in the table.
|
|
INSERT [dbo].[LC_Produkt_Dokument]
|
|
(
|
|
[LC_ProduktNr],
|
|
[Bezeichnung],
|
|
[beschreibung],
|
|
[Erstellt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[Dokument]
|
|
)
|
|
VALUES
|
|
(
|
|
@iLC_ProduktNr,
|
|
@sBezeichnung,
|
|
@sbeschreibung,
|
|
@daErstellt_am,
|
|
@daMutiert_am,
|
|
@iMutierer,
|
|
@bAktiv,
|
|
@blobDokument
|
|
)
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
-- Get the IDENTITY value for the row just inserted.
|
|
SELECT @iLC_Produkt_DokumentNr=SCOPE_IDENTITY()
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Einsatzgebiet_SelectAll] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select all rows from the table 'LC_Einsatzgebiet'
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Einsatzgebiet_SelectAll]
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT all rows from the table.
|
|
SELECT
|
|
[LC_EinsatzgebietNr],
|
|
[LC_EinsatzgebietNr_Parent],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Erstllt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[Imagenr]
|
|
FROM [dbo].[LC_Einsatzgebiet]
|
|
ORDER BY
|
|
[LC_EinsatzgebietNr] ASC
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Einsatzgebiet_SelectOne] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will select an existing row from the table 'LC_Einsatzgebiet'
|
|
-- based on the Primary Key.
|
|
-- Gets: @iLC_EinsatzgebietNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Einsatzgebiet_SelectOne]
|
|
@iLC_EinsatzgebietNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- SELECT an existing row from the table.
|
|
SELECT
|
|
[LC_EinsatzgebietNr],
|
|
[LC_EinsatzgebietNr_Parent],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Erstllt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[Imagenr]
|
|
FROM [dbo].[LC_Einsatzgebiet]
|
|
WHERE
|
|
[LC_EinsatzgebietNr] = @iLC_EinsatzgebietNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Einsatzgebiet_Delete] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will delete an existing row from the table 'LC_Einsatzgebiet'
|
|
-- using the Primary Key.
|
|
-- Gets: @iLC_EinsatzgebietNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Einsatzgebiet_Delete]
|
|
@iLC_EinsatzgebietNr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- DELETE an existing row from the table.
|
|
DELETE FROM [dbo].[LC_Einsatzgebiet]
|
|
WHERE
|
|
[LC_EinsatzgebietNr] = @iLC_EinsatzgebietNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Einsatzgebiet_Update] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will update an existing row in the table 'LC_Einsatzgebiet'
|
|
-- Gets: @iLC_EinsatzgebietNr int
|
|
-- Gets: @iLC_EinsatzgebietNr_Parent int
|
|
-- Gets: @sBezeichnung varchar(50)
|
|
-- Gets: @sBeschreibung varchar(255)
|
|
-- Gets: @daErstllt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Gets: @iImagenr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Einsatzgebiet_Update]
|
|
@iLC_EinsatzgebietNr int,
|
|
@iLC_EinsatzgebietNr_Parent int,
|
|
@sBezeichnung varchar(50),
|
|
@sBeschreibung varchar(255),
|
|
@daErstllt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iImagenr int,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- UPDATE an existing row in the table.
|
|
UPDATE [dbo].[LC_Einsatzgebiet]
|
|
SET
|
|
[LC_EinsatzgebietNr_Parent] = @iLC_EinsatzgebietNr_Parent,
|
|
[Bezeichnung] = @sBezeichnung,
|
|
[Beschreibung] = @sBeschreibung,
|
|
[Erstllt_am] = @daErstllt_am,
|
|
[Mutiert_am] = @daMutiert_am,
|
|
[Mutierer] = @iMutierer,
|
|
[Aktiv] = @bAktiv,
|
|
[Imagenr] = @iImagenr
|
|
WHERE
|
|
[LC_EinsatzgebietNr] = @iLC_EinsatzgebietNr
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[pr_LC_Einsatzgebiet_Insert] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
---------------------------------------------------------------------------------
|
|
-- Stored procedure that will insert 1 row in the table 'LC_Einsatzgebiet'
|
|
-- Gets: @iLC_EinsatzgebietNr_Parent int
|
|
-- Gets: @sBezeichnung varchar(50)
|
|
-- Gets: @sBeschreibung varchar(255)
|
|
-- Gets: @daErstllt_am datetime
|
|
-- Gets: @daMutiert_am datetime
|
|
-- Gets: @iMutierer int
|
|
-- Gets: @bAktiv bit
|
|
-- Gets: @iImagenr int
|
|
-- Returns: @iLC_EinsatzgebietNr int
|
|
-- Returns: @iErrorCode int
|
|
---------------------------------------------------------------------------------
|
|
CREATE PROCEDURE [dbo].[pr_LC_Einsatzgebiet_Insert]
|
|
@iLC_EinsatzgebietNr_Parent int,
|
|
@sBezeichnung varchar(50),
|
|
@sBeschreibung varchar(255),
|
|
@daErstllt_am datetime,
|
|
@daMutiert_am datetime,
|
|
@iMutierer int,
|
|
@bAktiv bit,
|
|
@iImagenr int,
|
|
@iLC_EinsatzgebietNr int OUTPUT,
|
|
@iErrorCode int OUTPUT
|
|
AS
|
|
SET NOCOUNT ON
|
|
-- INSERT a new row in the table.
|
|
INSERT [dbo].[LC_Einsatzgebiet]
|
|
(
|
|
[LC_EinsatzgebietNr_Parent],
|
|
[Bezeichnung],
|
|
[Beschreibung],
|
|
[Erstllt_am],
|
|
[Mutiert_am],
|
|
[Mutierer],
|
|
[Aktiv],
|
|
[Imagenr]
|
|
)
|
|
VALUES
|
|
(
|
|
@iLC_EinsatzgebietNr_Parent,
|
|
@sBezeichnung,
|
|
@sBeschreibung,
|
|
@daErstllt_am,
|
|
@daMutiert_am,
|
|
@iMutierer,
|
|
@bAktiv,
|
|
@iImagenr
|
|
)
|
|
-- Get the Error Code for the statement just executed.
|
|
SELECT @iErrorCode=@@ERROR
|
|
-- Get the IDENTITY value for the row just inserted.
|
|
SELECT @iLC_EinsatzgebietNr=SCOPE_IDENTITY()
|
|
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_lc_get_Struktur_Auswahl] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[sp_lc_get_Struktur_Auswahl]
|
|
@selectiontype AS integer
|
|
AS
|
|
BEGIN
|
|
DECLARE @tmp1 TABLE
|
|
(
|
|
id INT ,
|
|
parentid INT ,
|
|
bezeichnung VARCHAR(255) ,
|
|
imageindex INT ,
|
|
beschreibung VARCHAR(1024) ,
|
|
IstStruktur BIT ,
|
|
verweisnr INT ,
|
|
copy INT ,
|
|
originalid INT,
|
|
eingesetzt int
|
|
)
|
|
|
|
SET NOCOUNT ON;
|
|
-- Aktualisierung Produktestamm aus Applikation
|
|
UPDATE lc_produkt
|
|
SET bezeichnung = ( SELECT bezeichnung
|
|
FROM applikation
|
|
WHERE verweisnr = applikation.applikationnr
|
|
)
|
|
WHERE verweisnr > 0
|
|
UPDATE lc_produkt
|
|
SET mutiert_am = GETDATE() ,
|
|
aktiv = 0
|
|
WHERE verweisnr > 0
|
|
AND verweisnr NOT IN ( SELECT applikationnr
|
|
FROM applikation )
|
|
|
|
|
|
|
|
-- Alle Produkte
|
|
|
|
INSERT @tmp1
|
|
SELECT lc_produktnr AS id ,
|
|
lc_produktnr_parent AS parentid ,
|
|
bezeichnung ,
|
|
imageindex AS imageindex ,
|
|
ISNULL(Beschreibung, '') AS Beschreibung ,
|
|
IstStruktur ,
|
|
ISNULL(verweisnr, 0) AS Verweisnr ,
|
|
0 AS copy ,
|
|
LC_ProduktNr,
|
|
ISNULL(StatusNr,0)
|
|
FROM dbo.LC_Produkt
|
|
WHERE aktiv = 1
|
|
|
|
INSERT @tmp1
|
|
( id ,
|
|
parentid ,
|
|
bezeichnung ,
|
|
imageindex ,
|
|
beschreibung ,
|
|
IstStruktur ,
|
|
verweisnr ,
|
|
copy,
|
|
eingesetzt
|
|
)
|
|
VALUES ( 0 ,
|
|
NULL ,
|
|
'Root' ,
|
|
1 ,
|
|
'' ,
|
|
1 ,
|
|
0 ,
|
|
0,
|
|
0
|
|
)
|
|
|
|
UPDATE @tmp1
|
|
SET imageindex = imageindex + 0
|
|
WHERE id IN ( SELECT lc_produktnr
|
|
FROM lc_produkt
|
|
WHERE IstStruktur = 1 )
|
|
|
|
INSERT @tmp1
|
|
( id ,
|
|
parentid ,
|
|
bezeichnung ,
|
|
imageindex ,
|
|
beschreibung ,
|
|
IstStruktur ,
|
|
verweisnr ,
|
|
copy ,
|
|
Originalid,
|
|
eingesetzt
|
|
)
|
|
SELECT dbo.LC_Produkt_Copy.lc_produkt_copynr AS id ,
|
|
dbo.LC_Produkt_Copy.lc_produkt_parentnr AS parentid ,
|
|
dbo.LC_Produkt.Bezeichnung ,
|
|
dbo.LC_Produkt.Imageindex ,
|
|
dbo.LC_Produkt.Beschreibung ,
|
|
0 ,
|
|
0 ,
|
|
1 ,
|
|
lc_produkt.LC_ProduktNr,
|
|
ISNULL(lc_produkt.StatusNr,0)
|
|
FROM dbo.LC_Produkt_Copy
|
|
INNER JOIN dbo.LC_Produkt ON dbo.LC_Produkt_Copy.lc_produktnr = dbo.LC_Produkt.LC_ProduktNr
|
|
WHERE ( dbo.LC_Produkt_Copy.aktiv = 1 )
|
|
AND ( dbo.LC_Produkt.Aktiv = 1 )
|
|
|
|
IF @selectiontype=1 BEGIN
|
|
DELETE FROM @tmp1 WHERE IstStruktur=0 AND eingesetzt=0
|
|
INSERT @tmp1
|
|
( id ,
|
|
parentid ,
|
|
bezeichnung ,
|
|
imageindex ,
|
|
beschreibung ,
|
|
IstStruktur ,
|
|
verweisnr ,
|
|
copy ,
|
|
Originalid,
|
|
eingesetzt
|
|
)
|
|
SELECT lc_produktnr AS id ,
|
|
lc_produktnr_parent AS parentid ,
|
|
bezeichnung ,
|
|
imageindex AS imageindex ,
|
|
ISNULL(Beschreibung, '') AS Beschreibung ,
|
|
IstStruktur ,
|
|
ISNULL(verweisnr, 0) AS Verweisnr ,
|
|
0 AS copy ,
|
|
LC_ProduktNr,
|
|
ISNULL(StatusNr,0)
|
|
FROM dbo.LC_Produkt
|
|
WHERE aktiv = 1 AND lc_produktnr NOT IN (SELECT id FROM @tmp1) AND lc_produktnr IN (SELECT parentid FROM @tmp1)
|
|
|
|
END
|
|
IF @selectiontype=2 BEGIN
|
|
DELETE FROM @tmp1 WHERE IstStruktur=0 AND eingesetzt=1
|
|
INSERT @tmp1
|
|
( id ,
|
|
parentid ,
|
|
bezeichnung ,
|
|
imageindex ,
|
|
beschreibung ,
|
|
IstStruktur ,
|
|
verweisnr ,
|
|
copy ,
|
|
Originalid,
|
|
eingesetzt
|
|
)
|
|
SELECT lc_produktnr AS id ,
|
|
lc_produktnr_parent AS parentid ,
|
|
bezeichnung ,
|
|
imageindex AS imageindex ,
|
|
ISNULL(Beschreibung, '') AS Beschreibung ,
|
|
IstStruktur ,
|
|
ISNULL(verweisnr, 0) AS Verweisnr ,
|
|
0 AS copy ,
|
|
LC_ProduktNr,
|
|
ISNULL(StatusNr,0)
|
|
FROM dbo.LC_Produkt
|
|
WHERE aktiv = 1 AND lc_produktnr NOT IN (SELECT id FROM @tmp1) AND lc_produktnr IN (SELECT parentid FROM @tmp1)
|
|
end
|
|
|
|
SELECT *
|
|
FROM @tmp1
|
|
ORDER BY bezeichnung
|
|
|
|
end
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_lc_get_Struktur_Einsatzgebiet] Script Date: 02.05.2015 09:42:46 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[sp_lc_get_Struktur_Einsatzgebiet]
|
|
AS
|
|
BEGIN
|
|
declare @tmp1 TABLE (id int, parentid int, bezeichnung varchar(255),imageindex int)
|
|
SET NOCOUNT ON;
|
|
|
|
-- Insert statements for procedure here
|
|
INSERT @tmp1
|
|
SELECT [LC_EinsatzgebietNr] AS ID
|
|
,[LC_EinsatzgebietNr_Parent] AS PARENTID
|
|
,[Bezeichnung] AS BEZEICHNUNG
|
|
,imagenr AS imageindex
|
|
|
|
FROM [dbo].[LC_Einsatzgebiet]
|
|
WHERE Aktiv = 1
|
|
|
|
INSERT @tmp1 ( id, parentid, bezeichnung, imageindex)
|
|
VALUES ( 0, NULL, 'Root', 1)
|
|
|
|
|
|
SELECT *
|
|
FROM @tmp1
|
|
ORDER BY bezeichnung
|
|
|
|
|
|
END
|
|
|
|
GO
|
|
|