Teste:
CREATE PROCEDURE [dbo].[spGetItemsHeader]
(
@Name varchar(50) = NULL,
@Date datetime = NULL,
@Active smallint = NULL
)
AS
BEGIN
SELECT idItemHeader,
dsName,
dsUrl,
dtVigenciaIni,
dtVigenciaFim,
nuPrioridade,
dsType
FROM tbItemsHeader
WHERE (dsName = NULL OR dsName LIKE '%' + @Name + '%')
AND (@Date BETWEEN dtVigenciaIni and dtVigenciaFim)
AND
(
(@Active = 0 and GETDATE() NOT BETWEEN dtVigenciaIni AND dtVigenciaFim)
or
(@Active = 1 and GETDATE() BETWEEN dtVigenciaIni AND dtVigenciaFim)
)
Abraços, Weslley Moura MCP - MCTS - ORA SqlExpert