SQL select all if parameter is null else return specific item

by

Use case statement:

SELECT ProductID, ProductName,ProductDesc 
FROM product 
WHERE ProductID = CASE WHEN @productID IS NULL THEN ProductID ELSE @productID END

Or IIF() function if you’re using SQL Server 2012:

SELECT ProductID, ProductName,ProductDesc 
FROM product 
WHERE ProductID =IIF(@productID IS NULL, ProductID, @productID )

More Related Contents:

  1. nvarchar concatenation / index / nvarchar(max) inexplicable behavior
  2. How do I get list of all tables in a database using TSQL?
  3. Storing images in SQL Server?
  4. Escape a string in SQL Server so that it is safe to use in LIKE expression
  5. Limitations of SQL Server Express
  6. SQL time difference between two dates result in hh:mm:ss
  7. Storing files in SQL Server
  8. Dynamic SQL (passing table name as parameter)
  9. Dapper.NET and stored proc with multiple result sets
  10. “order by newid()” – how does it work?
  11. T-SQL Dynamic SQL and Temp Tables
  12. Efficient ISNUMERIC() replacements on SQL Server?
  13. How to determine total number of open/active connections in ms sql server 2005
  14. Login to Microsoft SQL Server Error: 18456
  15. Find non-ASCII characters in varchar columns using SQL Server
  16. Tablix: Repeat header rows on each page not working – Report Builder 3.0
  17. Violation of UNIQUE KEY constraint on INSERT WHERE COUNT(*) = 0 on SQL Server 2005
  18. ORDER BY DATE showing NULLS first then most recent dates
  19. How can I determine in SQL Server if a dateTime range overlaps another
  20. How to split a string after specific character in SQL Server and update this value to specific column
  21. SQL Server Configuration Manager cannot be found
  22. How can I run just the statement my cursor is on in SQL Server Management Studio?
  23. Set database collation in Entity Framework Code-First Initializer
  24. How do you get to limits of 8060 bytes per row and 8000 per (varchar, nvarchar) value?
  25. Can I have a foreign key referencing a column in a view in SQL Server?
  26. 7645 Null or empty full-text predicate
  27. How to get a list of all the MS SQL Server instances on the local machine?
  28. mysqldump equivalent for SQL Server
  29. What is Multi Dimension OLAP CUBE and give example cube with more than 3 dimensions
  30. The Best Way to shred XML data into SQL Server database columns

Leave a Comment