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 LIKE ' + @sexID

IF @statusID <> '0'

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

IF LEN(@firstname) > 0

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

IF LEN(@middlename) > 0

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

IF LEN(@lastname) > 0

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

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

@xmiddlename nvarchar(50),

@xlastname nvarchar(50),

@xsexID nchar(5),

@xstatusID nchar(5) '

EXEC sp_executesql @sqlquery,@SqlParam,

@firstname, @middlename,

@lastname, @sexID,

@statusID

END

How about this??..huhuhuhuh ;-(