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.
41 lines
1.4 KiB
41 lines
1.4 KiB
UPDATE dbo.Vertragspartner SET aktiv=0, mutiert_am=GETDATE() WHERE vertragspartnernr<>1 AND aktiv=1
|
|
go
|
|
UPDATE person SET aktiv=0, Mutiert_am=GETDATE() WHERE Vertragspartnernr<>1 AND aktiv=1
|
|
go
|
|
UPDATE ApplikationKontakt SET aktiv=0, mutiert_am = GETDATE() WHERE aktiv=1 AND personnr IN (SELECT personnr FROM person WHERE vertragspartnernr <> 1)
|
|
go
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_get_vertragspartner] Script Date: 10.08.2018 07:43:07 ******/
|
|
DROP PROCEDURE [dbo].[sp_get_vertragspartner]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_get_vertragspartner] Script Date: 10.08.2018 07:43:07 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
-- =============================================
|
|
-- Author: <Stefan Hutter>
|
|
-- Create date: <09.03.2009>
|
|
-- Description: <Liefert eine Liste der Vertragspartner>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[sp_get_vertragspartner]
|
|
@mitarbeiternr INT,
|
|
@suchstring VARCHAR(255)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SET @suchstring='%'+@suchstring+'%'
|
|
SELECT VertragspartnerNr ,NameZ1 + ' ' + NameZ2 + ', ' + PLZ + ' ' + Ort AS Description, aktiv
|
|
FROM dbo.Vertragspartner
|
|
WHERE dbo.vertragspartner.securitylevelnr <= dbo.get_securitylevel(@mitarbeiternr) AND
|
|
(namez1 LIKE @suchstring OR namez2 LIKE @suchstring OR ort LIKE @suchstring)
|
|
AND dbo.Vertragspartner.VertragspartnerNr=1
|
|
ORDER BY namez1,namez2, ort
|
|
END
|
|
|
|
GO
|