1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
| CREATE PROCEDURE GetList
(
@Param1 INT,
@Param2 INT,
@ParamZ BIT
)
AS
BEGIN
SET NOCOUNT ON;
/*Copie de Table1*/
CREATE TABLE TempTable(
Col1 [int] NOT NULL,
...
Col8 [int]
)
INSERT TempTable
SELECT
Table1.Col1,
...
Table1.Col8
FROM Table1
INNER JOIN Table2 ON Table1.Col1 = Table2.Col2
WHERE Table2.ColA = @Param1 AND Table2.ColB = @Param2
ORDER BY Table1.Col1 ASC
IF @ParamZ>0 AND EXISTS (SELECT ColX,ColY FROM Table3 WHERE ColZ = @ParamZ)
BEGIN
DECLARE @ColX INT
DECLARE @ColY INT
SELECT @ColX=ColX, @ColY=ColY FROM Table3 WHERE ColZ = @ParamZ;
IF @ColX IS NOT NULL
DELETE T1 FROM TempTable T1
INNER JOIN Table2 ON Table1.Col1 = Table2.Col2
WHERE Table2.ColX <> @ColX
IF @ColY IS NOT NULL
DELETE T1 FROM TempTable T1
INNER JOIN Table2 ON Table1.Col1 = Table2.Col2
WHERE Table2.ColY <> @ColY
END
SELECT * FROM TempTable
DROP TABLE TempTable
END |
Partager