Stored Procedure with optional “WHERE” parameters

by

One of the easiest ways to accomplish this:

SELECT * FROM table 
WHERE ((@status_id is null) or (status_id = @status_id))
and ((@date is null) or ([date] = @date))
and ((@other_parameter is null) or (other_parameter = @other_parameter))

etc.
This completely eliminates dynamic sql and allows you to search on one or more fields. By eliminating dynamic sql you remove yet another security concern regarding sql injection.

More Related Contents:

  1. update columns values with column of another table based on condition [duplicate]
  2. How do I list all the columns in a table?
  3. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  4. Is there a combination of “LIKE” and “IN” in SQL?
  5. SQL – How to store and navigate hierarchies?
  6. Can a foreign key be NULL and/or duplicate?
  7. Emulate MySQL LIMIT clause in Microsoft SQL Server 2000
  8. Multiplication aggregate operator in SQL
  9. SQL: Repeat a result row multiple times, and number the rows
  10. How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  11. MySQL equivalent of DECODE function in Oracle
  12. Deferrable Constraints in SQL Server
  13. Benefits Of Using SQL Ordinal Position Notation?
  14. How do I specify date literal when writing SQL query from SQL Server that is linked to Oracle?
  15. Unordered results in SQL
  16. How do I create a table alias in MySQL
  17. Backup SQL Schema Only?
  18. Connecting to Oracle Database using Sql Server Integration Services
  19. Why in SQL NULL can’t match with NULL?
  20. Is there an Oracle equivalent to SQL Server’s OUTPUT INSERTED.*?
  21. How to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field? [duplicate]
  22. Query to get only numbers from a string
  23. Paging with Oracle
  24. Error 1046 No database Selected, how to resolve?
  25. Calculating difference between two timestamps in Oracle in milliseconds
  26. Identity column value suddenly jumps to 1001 in sql server [duplicate]
  27. Why do you create a View in a database?
  28. Use Composite Primary Key as Foreign Key
  29. RODBC Temporary Table Issue when connecting to MS SQL Server
  30. SQL Inner Join On Null Values

Leave a Comment