SQL Server › Dynamic SQL

How would you build a dynamic SQL statement inside a stored procedure that constructs a flexible search query based on several optional filter parameters?

Advanced

Answer

You build the base query as a string and conditionally append additional WHERE clause segments only for the filter parameters that were actually provided, using parameterized values throughout with sp_executesql, letting a single stored procedure handle many different combinations of search criteria without writing a separate query for every possible combination.

CREATE PROCEDURE SearchOrders
  @CustomerId INT = NULL,
  @Status VARCHAR(20) = NULL
AS
BEGIN
  DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Orders WHERE 1=1';
  IF @CustomerId IS NOT NULL SET @SQL += N' AND CustomerId = @CustId';
  IF @Status IS NOT NULL SET @SQL += N' AND Status = @Stat';
  EXEC sp_executesql @SQL, N'@CustId INT, @Stat VARCHAR(20)', @CustId = @CustomerId, @Stat = @Status;
END;

Real-world example A customer service tool uses a single flexible search stored procedure built with dynamic SQL, letting agents search orders by customer, status, or both together, without needing several separate hardcoded procedures for each combination.

Common follow-ups: What is the WHERE 1=1 trick used for in this kind of dynamic query building?;How do you handle a large number of optional filters without the dynamic SQL becoming difficult to maintain?

Stored Procedures & Functions;Query Optimization & Plans