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