An SQL statement that retrieves a commodity based on the product name and price range

If the name and price are empty, all the data is retrieved

1  1 Select *  fromtblproduct2  2 where(@proName="' orChvproname like '%'+@proName+'%')3  3  and(@minPrice="'  orMnyproprice>=@minPrice)4  4  and(@maxPrice="' orMnyproprice<=@maxPrice);5  5 6  6--Replace the arguments with strings below7  7 --Test8  8 Select *  fromtblproduct9  9 where("'="' orChvproname like '%'+"'+'%')Ten Ten  and("'="'  orMnyproprice>=null) One  One  and("'="' orMnyproprice<=null);
--Retrieve all the goods

Note: ' Is null ' returns false

--If you change to the following code: "Error converting from data type varchar to numeric" is reported. "The WrongSelect *  fromtblproductwhere(' '=' ' orChvproname like '%'+"'+'%') and(' '  is NULL  orMnyproprice>= ") and(' '  is NULL orMnyproprice<= ");

If you change mnyproprice>= ' to Mnyproprice>=null, you will not get an error, but you cannot retrieve any data because the condition is not met.

An SQL statement that retrieves a commodity based on the product name and price range