set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- =============================================

-- Author:<Author,,Name>

-- Create date: <Create Date,,>

-- Description:<Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[SearchBiography]

@firstname nvarchar(50),

@middlename nvarchar(50),

@lastname nvarchar(50),

@sexID nchar(5) = NULL,

@statusID nchar(5) = NULL

AS

BEGIN

SET NOCOUNT ON;

DECLARE @sqlquery nvarchar(max) , @SqlParam nvarchar(max)

SET @sqlquery = ''

SELECT @sqlquery = ' SELECT * ' + ' FROM TestMyView WHERE 1 = 1 '

IF @sexID <> '0'

SELECT @sqlquery = @sqlquery + ' AND sexID = @xsexID '

IF @statusID <> '0'

SELECT @sqlquery = @sqlquery + ' AND statusID = @xstatusID '

IF LEN(@firstname) > 0

SELECT @sqlquery = @sqlquery + ' AND firstname LIKE ''%'' + @xfirstname + ''%'' '

IF LEN(@middlename) > 0

SELECT @sqlquery = @sqlquery + ' AND middlename LIKE ''%'' + @xmiddlename + ''%'' '

IF LEN(@lastname) > 0

SELECT @sqlquery = @sqlquery + ' AND lastname LIKE ''%'' + @xlastname + ''%'' '

SELECT @SqlParam = '@xfirstname nvarchar(50),

@xmiddlename nvarchar(50),

@xlastname nvarchar(50),

@xsexID nchar(1),

@xstatusID nchar(1) '

EXEC sp_executesql @sqlquery,@SqlParam,

@xfirstname = @firstname, @xmiddlename = @middlename,

@xlastname = @lastname, @xsexID = @sexID,

@xstatusID = @statusID

END

by reference to : http://www.sommarskog.se/dyn-search-2005.html and http://www.sommarskog.se/dynamic_sql.html