Search Query With Optional Parameters

I need to create a stored procedure that will search some tables.

The stored procedure will be passed some parameters that may or may not have a value.

I have googled the best way to do this.
I found this post as an example: Optional Search Parameters

and also found this example : Optional Parameters in T-sQL

I am trying to figure out the best way to do this.

In the past I would build a dynamic query like the following.


SQL Code:






Original
- SQL Code




CREATE PROCEDURE [dbo].[Search_Results]

@SUBCITY VarChar(100) = 'Any'

AS

------------------------------------------------------------------------------------------------------
Declare @SUBCITYString Varchar(200)
If @SUBCITY <> 'Any'
Begin
Set @SUBCITYString = ' AND (Table1.SUBCITY LIKE ''' + @SUBCITY + '%'') '
End
Else
Begin
Set @SUBCITYString = ''
End
-----------------------------------------------------------------------------------------------------

Declare @SQLString As Varchar(500)
Set @SQLString = 'SELECT*

FROMTable1

WHERE Table1.ID IS NOT NULL
' + @SUBCITYString + '

ORDER BY Column ASC'

Execute (@SQLString)

GO






CREATE PROCEDURE [dbo].[Search_Results]  @SUBCITY VarChar(100) = 'Any' AS ------------------------------------------------------------------------------------------------------DECLARE @SUBCITYString Varchar(200)IF @SUBCITY <> 'Any' BEGIN  SET @SUBCITYString = ' AND (Table1.SUBCITY  LIKE ''' + @SUBCITY + '%'') 'ENDELSE BEGIN SET @SUBCITYString = '' END----------------------------------------------------------------------------------------------------- DECLARE @SQLString AS Varchar(500)SET @SQLString = '  SELECT    *                            FROM  Table1               WHERE  Table1.ID IS NOT NULL                ' + @SUBCITYString +  '            ORDER BY Column ASC' Execute (@SQLString) GO


However this is really cumbersome to create and is not fun debugging!

Does one of these ways have an advantage over the other? Or is there another way to do this?

Thank you!