OK, Larry, here is an old teaching example I've used in the past, against the AdventureWorks DB. Idea is to query the Person.Address table based on either the AddressLine1...and/or the City...and/or the PostalCode

The proc received three parameters. It declares a variable and then dynamically constructs the query based on what someone passed in.

Now...I did this example where an empty string was passed. You can obviously define parameter defaults with NULL values instead, and then check for NULLs on the inside. At the end, it calls the system proc sp_executesql and passes the string.

So there are many ways you could write this, and I'm sure others will jump in with suggestions, but hopefully this will get you started.

There's an example at the end for how to call it...it will retrieve names with the city having the word 'Bay' somewhere in it...and having an address line with the word 'Way' somewhere in it. Hope this helps..

Also...I definitely recommend doing a bit of reading on SQL injection attacks.

use AdventureWorks2008
GO

CREATE PROCEDURE GetAddressData_DynamicSQL
@AddressLine1 varchar(100), @City varchar(100), @PostalCode varchar(100)
as
begin
   DECLARE @SqlSyntax nvarchar(4000)    -- when we create a variable to hold SQL syntax, we store as unicode
   SET @SqlSyntax = N'SELECT * FROM Person.Address '
   
   DECLARE @WhereSyntax nvarchar(4000)
   set @WhereSyntax = N''
   
   
   IF len(rtrim(@AddressLine1)) > 0
		SET @WhereSyntax = @WhereSyntax + ' AddressLine1 LIKE ''%' + @AddressLine1 + '%'' AND '
   IF len(rtrim(@City)) > 0
		SET @WhereSyntax = @WhereSyntax + ' City LIKE ''%' + @City + '%'' AND '
   IF len(rtrim(@PostalCode)) > 0
		SET @WhereSyntax = @WhereSyntax + ' PostalCode LIKE ''%' + @PostalCode + '%'' AND '
		
   IF LEN(RTRIM(@WhereSyntax)) > 0
		set @SqlSyntax = @SqlSyntax + ' WHERE ' + SUBSTRING(@WhereSyntax, 1, LEN(@WhereSyntax)-4)
		
		
   execute sp_executesql @SqlSyntax
   		
end
go
		
		
exec GetAddressData_DynamicSQL @AddressLine1 = 'Way', @City = 'Bay', @PostalCode = ''