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 SET @FName = UPPER(@FName) SET @LName = UPPER(@LName) SET @Age = UPPER(@Age) SET @Street = UPPER(@Street) SET @City = UPPER(@City) SET @State = UPPER(@State) DECLARE @Sql NVARCHAR(2000) DECLARE @Where NVARCHAR(1000) = '' 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 SET @Where = @Where + 'AND Sex = ''M'' ' 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 ' 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 ' 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' EXEC sp_executesql @SQL, N'@_FName VARCHAR(50), @_LName VARCHAR(50), @_IsMale BIT, @_IsFemale BIT, @_Age VARCHAR(3), @_Address_Number NVARCHAR(50), @_Street NVARCHAR(50), @_City NVARCHAR(50), @_State CHAR(2), @_Zip VARCHAR(5), @_IsDemocrat BIT, @_IsRepublican BIT, @_IsIndependent BIT', @_FName = @FName, @_LName = @LName, @_IsMale = @IsMale, @_IsFemale = @IsFemale, @_Age = @Age, @_Address_Number = @Address_Number, @_Street = @Street, @_City = @City, @_State = @State, @_Zip = @Zip, @_IsDemocrat = @IsDemocrat, @_IsRepublican = @IsRepublican, @_IsIndependen = @IsIndependentWhen I run this
EXEC sproc_GetContacts @FName = 'WILLIAM', @LName = 'BRAY', @IsDemocrat=1, @IsRepublican=1, @IsIndependent=1I get
The parameterized query '(@_FName VARCHAR(50), @_LName VARCHAR(50), @_IsMale BIT, @' expects the parameter '@_IsIndependent', which was not supplied.The table has a column called 'Party', and if I passed in IsDemocrat, then any row with a 'D' in the Party field should be retured. Same for the IsRepublican or IsIndepdendent ('R' and 'U').