Nawigacja

sp_adMotoRaportProwizja.txt

Data:

2009-02-16 13:31:06 (17 years ago)

Autor:

marek

Opis:

re #92

Pliki:

r352	r355
1		~~-- =============================================~~
2		~~-- Create basic stored procedure template~~
3		~~-- =============================================~~
4	1
5		-- Drop stored procedure if it already exists
6		IF EXISTS (
7		SELECT *
8		FROM INFORMATION_SCHEMA.ROUTINES
9		WHERE SPECIFIC_SCHEMA = N'dbo'
10		AND SPECIFIC_NAME = N'sp_adMotoRaportProwizja'
11		)
12		DROP PROCEDURE dbo.sp_adMotoRaportProwizja
	2	SET ANSI_NULLS ON
	3	GO
	4	SET QUOTED_IDENTIFIER ON
13	5	GO
14	6
15		CREATE PROCEDURE dbo.sp_adMotoRaportProwizja
	7	ALTER PROCEDURE [dbo].[sp_adMotoRaportProwizja]
16	8	@rok int,
17	9	@miesiac int,
18		@tytul nvarchar(100)
	10	@tytul nvarchar(100),
	11	@prowizjaPoz money,
	12	@prowizjaKat money
19	13	AS
20
21	14	DECLARE @Wplywy TABLE
22	15	(
…	…
60	53	AND (Tytul IN (@tytul))
61	54
	55	IF @prowizjaPoz IS NOT NULL
	56	BEGIN
	57	UPDATE @Wplywy
	58	SET Prowizja=ROUND(NETTO * @prowizjaPoz, 2)
	59	WHERE Agencja='GS O/POZNAÑ'
	60	END
	61
	62	IF @prowizjaKat IS NOT NULL
	63	BEGIN
	64	UPDATE @Wplywy
	65	SET Prowizja=ROUND(NETTO * @prowizjaKat, 2)
	66	WHERE Agencja='GS O/KATOWICE'
	67	END
	68
	69
62	70	UPDATE @Wplywy
63	71	SET Netto = NULL
…	…
86	94	WHERE IdFaktury NOT IN (SELECT IdFaktury FROM @Wplywy)
87	95	ORDER BY DataWplaty, Agent, NumerFaktury
88
89
90	96	GO
91	97
92		-- =============================================
93		-- Example to execute the stored procedure
94		-- =============================================
95		EXECUTE dbo.sp_adMotoRaportProwizja 2009, 1, 'adMot'
	98	SET ANSI_NULLS OFF
96	99	GO
	100	SET QUOTED_IDENTIFIER OFF
	101	GO
	102