Menu
When searching for a list of products, the @SearchType parameter is optional. If @SearchType is empty or NULL then it should return all products and not use the WHERE clause. Otherwise, if it passed Equipment it would then use that instead.
ALTER PROCEDURE [dbo].[psProducts] (@SearchType varchar(50)) AS BEGIN SET NOCOUNT ON; SELECT P.[ProductId], P.[ProductName], P.[ProductPrice], P.[Type] FROM [Product] P -- if @Searchtype is not null then use the where clause WHERE p.[Type] = @SearchType END 
564
The IS NULL condition is used in SQL to test for a NULL value. It returns TRUE if a NULL value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
Inside the stored procedure, the parameter value is first tested for Null using the ISNULL function and then checked whether it is Blank (Empty). If the parameter has value then only matching records will be returned, while if the parameter is Null or Blank (Empty) then all records from the table will be returned.
We use IS NULL to identify NULL values in a table. For example, if we want to identify records in the employee table with NULL values in the Salary column, we can use IS NULL in where clause.
NULL is used in SQL to indicate that a value doesn't exist in the database. It's not to be confused with an empty string or a zero value. While NULL indicates the absence of a value, the empty string and zero both represent actual values.
Just use
If @searchType is null means 'return the whole table' then use
WHERE p.[Type] = @SearchType OR @SearchType is NULL If @searchType is an empty string means 'return the whole table' then use
WHERE p.[Type] = @SearchType OR @SearchType = '' If @searchType is null or an empty string means 'return the whole table' then use
WHERE p.[Type] = @SearchType OR Coalesce(@SearchType,'') = '' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
