Close

SQL Server Select Optional

Suppose you want to select rows in a table using optional parameters. That’s a quite common requirement, but not obvious to me. Here is an example query in a stored procedure. In the where clause ou can ycheck whether the attribute is empty or if there is an attribute match.

CREATE PROCEDURE [dbo].[sp_Select_Attributes]
(
    @AttributeId bigint = null,
	@AttributeName varchar(50) = null,
	@ApplicationCode varchar(20) = null
)
AS
BEGIN

	SET NOCOUNT ON

	SELECT * FROM Attributes
	WHERE (@AttributeId IS NULL OR (AttributeId = @AttributeId))
    AND (@AttributeName IS NULL OR (AttributeName = @AttributeName ))
    AND (@ApplicationCode IS NULL OR (ApplicationCode = @ApplicationCode))
    OPTION (RECOMPILE)

END
GO