CREATE PROCEDURE sproc_GetContacts @FName VARCHAR(50) = NULL, @LName VARCHAR(50) = NULL, @IsMale BIT = NULL, @IsFemale BIT = NULL, @Age VARCHAR(3) = NULL, @Address_Number NVARCHAR(50) = NULL, @Street NVARCHAR(50) = NULL, @City NVARCHAR(50) = NULL, @State CHAR(2) = NULL, @Zip VARCHAR(5) = NULL, @IsDemocrat BIT = NULL, @IsRepublican BIT = NULL, @IsIndependent BIT = NULL AS DECLARE @Sql NVARCHAR(max) DECLARE @Where NVARCHAR(max) = '' SET @Sql = 'SELECT VoterID, FName, LName, Sex, Age, Address_Number, Street, City, State, Zip, Party FROM tblCamp_CT ' IF @FName IS NOT NULL SET @Where = @Where + ' AND FName = @_FName ' IF @LName IS NOT NULL SET @Where = @Where + ' AND LName = @_LName ' IF @IsMale IS NOT NULL AND @IsMale = 1 AND @IsFemale IS NOT NULL AND @IsFemale = 1 -- this is wrong case -- we don't do anything here ELSE IF @IsMale IS NOT NULL AND @IsMale = 1 SET @Where = @Where + ' AND Sex = ''M'' ' ELSE IF @IsFemale IS NOT NULL AND @IsFemale = 1 SET @Where = @Where + ' AND Sex = ''F'' ' IF @Age IS NOT NULL SET @Where = @Where + ' AND Age = @_Age ' -- this is not clear - age is character? IF @Address_Number IS NOT NULL SET @Where = @Where + ' AND Address_Number = @_Address_Number ' IF @Street IS NOT NULL SET @Where = @Where + ' AND Street = @_Street ' IF @City IS NOT NULL SET @Where = @Where + ' AND City = @_City ' IF @State IS NOT NULL SET @Where = @Where + ' AND State = @_State ' IF @Zip IS NOT NULL SET @Where = @Where + ' AND Zip = @_Zip ' -- Hopefully you can only always chose one option from the interface, otherwise it makes no sense, so I'm not checking weird cases IF @IsDemocrat IS NOT NULL AND @IsDemocrat = 1 SET @Where = @Where + ' AND Party = ''D'' ' IF @IsRepublican IS NOT NULL AND @IsRepublican = 1 SET @Where = @Where + 'AND Party = ''R'' ' IF @IsIndependent IS NOT NULL AND @IsIndependent = 1 SET @Where = @Where + 'AND Party = ''U'' ' IF LEN(@Where) > 0 SET @Sql = @Sql + ' WHERE ' + RIGHT(@Where, LEN(@Where)-3) SET @Sql = @Sql + ' ORDER BY FName, LName' -- I'd order by Last Name first -- if we want to test, print @SQL first - this is how we debug it EXEC sp_executesql @SQL, N'@_FName VARCHAR(50), @_LName VARCHAR(50), @_Age VARCHAR(3), @_Address_Number NVARCHAR(50), @_Street NVARCHAR(50), @_City NVARCHAR(50), @_State CHAR(2), @_Zip VARCHAR(5)', @_FName = @FName, @_LName = @LName, @_Age = @Age, @_Address_Number = @Address_Number, @_Street = @Street, @_City = @City, @_State = @State, @_Zip = @ZipWhen I run this
EXEC sproc_GetContacts @FName = 'WILLIAM', @LName = 'BRAY', @IsDemocrat=1, @IsRepublican=1, @IsIndependent=1it should now work.