dwain.c (9/27/2013)
That is my recommendation.I find the resulting code to be a bit easier to understand.
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,
@statusID nchar
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlquery varchar(max)
SET @sqlquery = ''
SET @sqlquery = ' SELECT * FROM TestMyView ' +
CASE
WHEN @sexID <> 0 OR @statusID <> 0 OR LEN(@firstname) > 0 OR LEN(@middlename) > 0 OR LEN(@lastname) > 0
THEN ' WHERE 1=1 '
ELSE ''
END +
CASE
WHEN @sexID <> 0
THEN ' AND sexID = ' + convert(varchar(20), @sexID)
ELSE ''
END +
CASE
WHEN @statusID <> 0
THEN ' AND statusID = ' + convert(varchar(20), @statusID)
ELSE ''
END +
CASE
WHEN LEN(@firstname) > 0
THEN ' AND firstname like ''%' + @firstname + '%'''
ELSE ''
END +
CASE
WHEN LEN(@middlename) > 0
THEN ' AND middlename like ''%' + @middlename + '%'''
ELSE ''
END +
CASE
WHEN LEN(@lastname) > 0
THEN ' AND lastname like ''%' + @lastname + '%'''
ELSE ''
END
EXEC sp_executesql @sqlquery, N'@statusID=@statusID, @sexID=@sexID, @firstname=@firstname, @middlename=@middlename, @lastname=@lastname' -- one for each of your filters
,@sexID=@sexID
,@statusID=@statusID
,@firstname=@firstname
,@middlename=@middlename
,@lastname=@lastname
END
it wont work dwain 🙁 poor me...