Default parameters in Stored Procedure 1

[wmddgm]

Hi, i''m tring to get a stored procedure to return all records by default if no values are passed to it.

So i have:

Create Procedure qrySuppSearch

	(
		@Name nvarchar(50) = "%",
		@Addy nvarchar(50) = "%",
		@City nvarchar(50) = "%",
		@Country nvarchar(50) = "%",
		@TradeName nvarchar(50) = "%",
		@Contact nvarchar(50) = "%"
	)

As
SELECT tblSuppliers.SupplierID, tblSuppliers.SupplierName, tblSuppliers.Address, tblSuppliers.City, 
	tblSuppliers.Country, tblSuppliers.TradingName, tblSupplierContacts.ContactName
FROM tblSuppliers 
LEFT JOIN tblSupplierContacts ON tblSuppliers.SupplierID = tblSupplierContacts.SupplierID
WHERE ((supplierName like @Name)
	AND (Address like @Addy)
	AND (City like @City)
	AND (Country like @Country)
	AND (TradingName like @TradeName)
	AND (ContactName like @Contact))

But this doesn't return any records as every record has at least one null value in it (mainly the last two fields)
And if i add something like:

AND ((TradingName like @TradeName) OR (TradingName is NULL))

Then it still doesn't work as when i pass a value into the parameter it returns records with that value and null values...

 

[TheGreenOne]

If you think the problem is nulls try using the isnull() function to replace any null values in the where clause with an empty string like this:
WHERE ((isnull(supplierName,'') like @Name)
AND ((isnull(Address,'') like @Addy)
AND ((isnull(City,'') like @City)
AND ((isnull(Country,'') like @Country)
AND ((isnull(TradingName,'') like @TradeName)
AND ((isnull(ContactName,'') like @Contact))

Also, when you call it be sure not to pass empty strings in the arguments, this will replace your default value.