Menu
Can the following query be modified to return all records if the ? is null?
SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = ?; 
872
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.
If you want a parameter that can filter by a certain value, but when you have no value in your parameter, SQL returns no results. When the parameter has no value, SQL interprets it as null in your code. Null means no value. You can fix this problem by adding a code to fix the null case.
IF (@item1 IS NOT NULL) OR (LEN(@item1) > 0) SELECT @sql = 'SELECT * FROM TEST1' ELSE SELECT @sql = 'SELECT * FROM TEST2' PRINT @sql; @item1 is NVARCHAR(1000) type. It is somewhere a function in sql to verify if a string is null or empty OR I made somewhere a mistake ?
Description. 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.
Try this:
SELECT * FROM MY_TABLE WHERE @parameter IS NULL OR NAME = @parameter; 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
