October 29, 2010 at 1:26 pm
I am creating a sproc and am having complete brain lock. I am passing a parameter for a gender column that has two values, M or F. I also want to be able to pass parameter B for both, but I can not figure out how to make this happrn. I have tried CASE and IF statements in the WHERE clause with no luck. Could someone help me out on this?
Thanks.
Steve
October 29, 2010 at 1:43 pm
Can you post the table definition, some sample data (click on the first link in my signature block for a quick means of doing just this), expected results, and what T-SQL you have already written. With this information some one will be able to assist you with a tested solution.
Now making a lot of assumptions will this work for you
CREATE TABLE #T(Something VARCHAR(50), Gender CHAR(1))
INSERT INTO #T
SELECT 'Male','M' UNION ALL
SELECT 'Female','F' UNION ALL
SELECT 'UNK',NULL
DECLARE @Input CHAR(1)
SET @Input = 'B'
If @Input = 'M'
SELECT Something, gender FROM #T WHERE GENDER = 'M'
ELSE IF @Input = 'F'
SELECT Something, gender FROM #T WHERE GENDER = 'F'
ELSE
SELECT Something, gender FROM #T
Result:
Somethinggender
Male M
Female F
UNK NULL
Using this input only one row is returned:
DECLARE @Input CHAR(1)
SET @Input = 'M'
If @Input = 'M'
SELECT Something, gender FROM #T WHERE GENDER = 'M'
ELSE IF @Input = 'F'
SELECT Something, gender FROM #T WHERE GENDER = 'F'
ELSE
SELECT Something, gender FROM #T
REsult:
Somethinggender
Male M
October 29, 2010 at 1:46 pm
WHERE (@gender = 'B' OR gender = @gender)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 29, 2010 at 2:03 pm
Craig,
That worked! Thank you so much!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply