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
| ALTER PROCEDURE dbo.GetListeConges
@_Id numeric(18,0),
@_IdType numeric(18,0),
@_DateDebut datetime = NULL,
@_DateFin datetime = NULL
AS
BEGIN
IF @_DateDebut IS NULL AND @_DateFin IS NULL
BEGIN
RAISERROR('L''une des deux dates de début et de fin de période doivent être fournis', 16, 1)
RETURN
END
DECLARE @DateDebutChaine nchar(17)
, @DateFinChaine nchar(17)
SELECT @DateDebutChaine = CONVERT(nchar(8), @_DateDebut, 112) + ' ' + CONVERT(nchar(8), @_DateDebut, 108)
, @DateFinChaine = CONVERT(nchar(8), @_DateFin, 112) + ' ' + CONVERT(nchar(8), @_DateFin, 108)
DECLARE @sql nvarchar(1024)
SET @sql = 'SELECT IdConge FROM dbo.Conges WHERE IdUser = ' + CAST(@_Id AS varchar(18))
SELECT @sql = CASE WHEN @_IdType IS NOT NULL THEN ' AND IdTypeConge = ' + CAST(@_IdType AS varchar(18)) ELSE '' END
+ CASE WHEN @_DateDebut IS NOT NULL THEN ' AND DateDebutConge >= ' + @DateDebutChaine ELSE '' END
+ CASE WHEN @_DateFin IS NOT NULL THEN ' AND DateFinConge >= ' + @DateFinChaine ELSE '' END
SET @sql = @sql + ' ORDER BY DateDebutConge'
-- PRINT @sql
EXEC sp_executeSQL @sql
END |
Partager