Procedure Search by Keyword(s)

CREATE PROCEDURE [dbo].[utl_ProcSearch] 
	@cSearch1	VarChar(255)
	,@cSearch2	VarChar(255) = ''
AS
/********************************************************************************************************************************************************
Purpose: 	Handy little proc to search all sps for a piece of text
		Pass in two pieces of text to get sps that contain both

Amendment Log
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Date 		Who			Email
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16/11/00	Julian Haynes		me@julianhaynes.freeserve.co.uk
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
********************************************************************************************************************************************************/
SET NOCOUNT ON

IF @cSearch2 = ''

	SELECT DISTINCT
		LEFT(P.Name, 30) 'Procedure'
	FROM
		SysComments C
		JOIN SysObjects P
			ON P.ID = C.ID 
			AND (P.Type = 'P' OR P.Type = 'TR')
			AND Left(P.Name,3) <> 'dt_'
	WHERE
		C.Text LIKE '%' + @cSearch1 +'%'
	ORDER BY
		1
ELSE

	SELECT DISTINCT
		LEFT(P.Name, 30) 'Procedure'
	FROM
		SysComments C
		JOIN SysObjects P
			ON P.ID = C.ID 
			AND (P.Type = 'P' OR P.Type = 'TR')
			AND Left(P.Name,3) <> 'dt_'
	WHERE
		C.Text LIKE '%' + @cSearch1 +'%' AND C.Text LIKE '%' + @cSearch2 +'%'
	ORDER BY
		1