I want to create a procedure in SQL Server that will select and join two tables. The parameters @company, @from and @to are always set but @serie_type can be NULL. If @serie_type is not NULL i just want to include the specified types, simple AND S.Type = @serie_type
, but if @serie_type is NULL i want to include all types, simple, just dont include the AND statement. My problem is that i dont know if @serie_type will be set therefore i would like o have something like this:
/* pseudocode */
??? = AND (IF @serie_type IS NOT NULL S.Type = @serie_type)
Here is a simpifyed version of procedure:
CREATE PROCEDURE Report_CompanySerie
@company INT,
@serie_type INT,
@from DATE,
@to DATE
AS
BEGIN
SELECT
*
FROM Company C
JOIN Series S ON S.Company_FK = C.Id
WHERE C.Id = @company
AND S.Created >= @from
AND S.Created <= @to
/* HERE IS MY PROBLEM */
AND ???
END
GO
Don't want to duplicate the select becaust the real select is way bigger then this.
The common approach is:
WHERE
C.Id = @company
AND S.Created >= @from
AND S.Created <= @to
AND (@serie_type IS NULL OR S.Type = @serie_type)