November 19, 2009 at 8:46 am
I build my queries using the query analyser in Visual Studio, I add parameters in there by typing @parametername in the filter cell.
I have a parameter to filter by postcode, called @postcode.
I have it set to allow blanks, and it filters by postcode properly.
However if the user does not enter a postcode, it only returns records where the postcode cell is blank, I want it to return everything if the user does not enter a postcode.
Can anyone help?
Thanks,
Gillian
November 20, 2009 at 6:20 am
You have a couple of options:
1. Use an expression in the query editor to ignore the parameter if empty string. Something like this:
="Select columns from table" & IF(Parameters!PostCode.Value = "", "", " Where postcode = " & Parameters!PostCode.Value
2. Use a stored procedure and check the value of the parameter in the stored procedure like this
IF @PostCode = ''
BEGIN
SELECT
COLUMNs
FROM
TABLE
END
ELSE
BEGIN
SELECT
columns
FROM
TABLE
WHERE
PostCode = @PostCode
END
obviously neither of these is a tested solution, but I think both point you in the right direction.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 20, 2009 at 11:39 am
The way I typically handle it is similar to Jack's #2 option although I do the check in the where clause. So it would be more like
Select columns
From table
Where (@PostCode = '' or PostCodeColumn = @PostCode)
November 23, 2009 at 1:38 am
Thanks Guys,
I have it working now.
Gillian
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply