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 ;-(