Can I have optional parameters to my stored procedures?

Yes, you can have a stored procedure that has optional parameters. You can do this by setting a default value for each parameter that you want to make optional. The default value is typically NULL, but it doesn't have to be. Here is some sample code, ready to test in Query Analyzer: 
 

CREATE PROCEDURE dbo.foo      @param1 VARCHAR(32) = NULL,      @param2 INT = NULL  AS  BEGIN      SET NOCOUNT ON        SELECT Param1 = COALESCE      (          @param1,          '@param1 was empty'      )      SELECT Param2 = COALESCE      (          RTRIM(@param2),          '@param2 was empty'      )  END  GO    EXEC dbo.foo @param1='bar', @param2=4  EXEC dbo.foo @param1='bar'  EXEC dbo.foo @param2=4  EXEC dbo.foo 'bar',4  EXEC dbo.foo 'bar'    EXEC dbo.foo  GO    DROP PROCEDURE dbo.foo  GO

 
For a more realistic example, let's say you are using the Pubs database and want to return specific last names if a last name is entered, otherwise you want to return everyone. 
 

USE Pubs  GO    CREATE PROCEDURE dbo.GetAuthors      @lastName VARCHAR(32) = NULL  AS  BEGIN      SET NOCOUNT ON        SELECT * FROM Authors          WHERE AU_LName LIKE COALESCE(@lastName, '%')  END  GO

 
You may be tempted to use a slightly different method, e.g.: 
 

WHERE AU_LName = COALESCE(@LastName, AU_LName)    WHERE AU_LName = CASE WHEN @lastName IS NULL THEN AU_LName ELSE @LastName END

 
However, you will find the best execution plan is the method I demonstrated within the stored procedure. In the following example, you will see index seeks for GetAuthors1 and GetAuthors2, but index scans on the others (and you will see a big difference in the statistics and amount of work). Turn on Execution Plan (Ctrl+K) in Query Analyzer when running this script: 
 

USE Pubs  GO    CREATE PROCEDURE dbo.GetAuthors1      @lastName VARCHAR(32) = NULL  AS  BEGIN      SET NOCOUNT ON        SELECT * FROM Authors          WHERE AU_LName LIKE COALESCE(@lastName, '%')  END  GO    CREATE PROCEDURE dbo.GetAuthors2      @lastName VARCHAR(32) = NULL  AS  BEGIN      SET NOCOUNT ON        SELECT * FROM Authors          WHERE AU_LName LIKE ISNULL(@lastName, '%')  END  GO    CREATE PROCEDURE dbo.GetAuthors3      @lastName VARCHAR(32) = NULL  AS  BEGIN      SET NOCOUNT ON        SELECT * FROM Authors          WHERE AU_LName = COALESCE(@lastName, AU_LName)  END  GO    CREATE PROCEDURE dbo.GetAuthors4      @lastName VARCHAR(32) = NULL  AS  BEGIN      SET NOCOUNT ON        SELECT * FROM Authors          WHERE AU_LName = ISNULL(@lastName, AU_LName)  END  GO    CREATE PROCEDURE dbo.GetAuthors5      @lastName VARCHAR(32) = NULL  AS  BEGIN      SET NOCOUNT ON        SELECT * FROM Authors          WHERE AU_LName = CASE              WHEN @lastName IS NULL THEN AU_LName              ELSE @lastName END  END  GO      EXEC dbo.GetAuthors1  EXEC dbo.GetAuthors2  EXEC dbo.GetAuthors3  EXEC dbo.GetAuthors4  EXEC dbo.GetAuthors5    EXEC dbo.GetAuthors1 'Carson'  EXEC dbo.GetAuthors2 'Carson'  EXEC dbo.GetAuthors3 'Carson'  EXEC dbo.GetAuthors4 'Carson'  EXEC dbo.GetAuthors5 'Carson'  GO    DROP PROCEDURE dbo.GetAuthors1  DROP PROCEDURE dbo.GetAuthors2  DROP PROCEDURE dbo.GetAuthors3  DROP PROCEDURE dbo.GetAuthors4  DROP PROCEDURE dbo.GetAuthors5  GO

 
(For more information on COALESCE vs. ISNULL, see Article #2532.) 
 
Access 
For an Access stored query, you can use the following logic: 
 

PARAMETERS Param1 VARCHAR(32), Param2 INT;  SELECT columns FROM table WHERE      ([Param1] IS NULL OR column1 = [Param1])      AND      ([Param2] IS NULL OR column2 = [Param2])