Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
How To Pull Gender
Message
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Miscellaneous
Thread ID:
01510820
Message ID:
01510823
Views:
74
>I have the following. If I pass IsMale = 1, then the result set should include rows where Sex = 'M'. Same for IsFemale, but something isn't quite right.
>
>
>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,
>	@IsRepuplican BIT = NULL,
>	@IsIndepedent BIT = NULL
>	
>AS
>
>	SELECT	VoterID, FName, LName, Sex, Age, Address_Number, Street, City, State, Zip, Party
>		FROM tblCamp_CT
>		WHERE (@FName IS NULL OR FName = @FName) AND
>			  (@LName  IS NULL OR LName = @LName) AND
>			  (CASE @IsMale WHEN 1 THEN Sex = 'M' END) AND
>			  (CASE @IsFemale WHEN 1 THEN Sex = 'F' END) AND
>			  (@Age  IS NULL OR Age =  @Age) AND	
>			  (@Address_Number IS NULL OR Address_Number =  @Address_Number) AND
>			  (@Street IS NULL OR Street =  @Street) AND
>			  (@City  IS NULL OR City = @City) AND
>			  (@State  IS NULL OR State = @State) AND
>			  (@Zip IS NULL OR Zip = @Zip) AND
>			  (@Party  IS NULL OR Party = @Party)	
>		ORDER BY FName, LName
>
Your problem is in trying to do 'Catch all queries'. Rather than re-writing this query for you, I suggest you to take a minute to read these blogs to understand the best solution for the problem:
Do you use ISNULL(...). Don't, it does not perform - short blog by Denis Gobo
Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later) - long and comprehensive article by Erland Sommarskog 
Catch All Queries - short blog by Gail Shaw
Sunday T-SQL tip: How to select data with unknown parameter set Nice blog by Dmitri Korotkevitch
If it's not broken, fix it until it is.


My Blog
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform