Two optional parameters...if else endif

Question

Two optional parameters...if else endif

gallan-229957

SSCommitted

Points: 1520
More actions
July 7, 2005 at 6:11 pm

#109804

I've got two optional parameters but am not sure how to use them directly in the data pane of visual studio. I can use the iif statement but here's the logic I want to invoke:
if parameter1 <> ''
begin
  if parameter2 <> ''
    SELECT * FROM mytable WHERE p1 = parameter1 AND p2 = parameter1
  else
    SELECT * FROM mytable WHERE p1 = parameter1
elseif parameter2 <> ''
  SELECT * FROM mytable WHERE p2 = parameter2
else
  SELECT * FROM mytable
end

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

you could try dynamic sql...sooo, sort of like the following... Note that I've assumed you're talking string parms so have single quoted them accordingly. Either approach below will work, probably cleaner to use the top one (just appending a where clause where required).

= "SELECT * FROM my Table" & Iif(Parameters!Param1.Value <> String.Empty, Iif(Parameters!Param2.Value <> String.Empty, " WHERE p1 = '" & Parameters!Param1.Value & "' AND p2 = '" & Parameters!Param2.Value & "'", " WHERE p1 = '" & Parameters!Param1.Value & "'"), Iif(Parameters!Param2.Value <> String.Empty, " WHERE p2 = '" & Parameters!Param2.Value & "'", String.Empty)

= Iif(Parameters!Param1.Value <> String.Empty, Iif(Parameters!Param2.Value <> String.Empty, "SELECT * FROM myTable WHERE p1 = '" & Parameters!Param1.Value & "' AND p2 = '" & Parameters!Param2.Value & "'", "SELECT * FROM myTable WHERE p1 = '" & Parameters!Param1.Value & "'"), Iif(Parameters!Param2.Value <> String.Empty, "SELECT * FROM myTable WHERE p2 = '" & Parameters!Param2.Value & "'", SELECT * FROM myTable")

Also remember, if you're using dynamic sql, you need to have one continuous line (it will wrap). if you think this looks ugly (which it does), then use multiple lines but then use the Replace function (intrinsic to strings) to replace the Chr(10) and Chr(13) (ie carriage returns and line feeds).

HTH,

Steve.

gallan-229957 SSCommitted Points: 1520 More actions · Answer 2

That's great Steve they both work.

Now...hehe, how can I get the report to NOT run right away, i.e. I want it to wait to see if the user selects 0,1, or both parameters and then run. As it is now, the report will run with no parameters as soon as I click on the report link.

I should mention that the two parameters are filled from my db. I had to add a UNION with SELECT '' as 'NAME', NULL AS 'ID' from myTable in order to allow it to run with no parameters choosen even though I've got "Allow null value" and "Allow blank value" both selected. Without the union the report doesn't run right away but it prompts for a selection from the dropdowns.

Your help is very much appreciated.

BTW, how the weather down there? Rainy here in Vancouver BC, Canada.

Greg

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Hey Greg,

Not sure why i missed this but you could make this a lot simpler by using 'OR' statements.

it's no longer a dynamic sql statement, it's back to standard sql, which is nice 'cause it means you can now use the graphical designer and also named parameters I've assumed (from your last post) that the key/id is an INT (or similar), so you would need to change if its CHAR/VARCHAR etc. I've put both here (int field type first, char datatype second).

SELECT * FROM myTable WHERE (iField1 = @Param1 OR @Param1 = -1) AND (iField2 = @Param2 OR @Param2 = -1)

SELECT * FROM myTable WHERE (Field1 = @Param1 OR @Param1 = '') AND (Field2 = @Param2 OR @Param2 = '')

You'll obviously need to change your parameters source statements, but you'll still be using a UNION. I've put simple examples for the INT and CHaR types, obviously with 2 parms you'll need to queries.

SELECT -1 AS [ID], '[None]' AS MyDescription

UNION ALL

SELECT [ID], [MyDescription] FROM myTable

SELECT '' AS [ID], '[None]' AS MyDescription

UNION ALL

SELECT [ID], [MyDescription] FROM myTable

You can now leave out the allow blanks/nulls from the parms, plu make sure you don't set a default. This should result in the report loading up but not executiing until the user selects a value (be it 'none' or a "valid" value form the table).

Weather here is great (think Florida without the hurricanes or lightning).

Hope this helps,

Steve.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

Forgot to mention, in case you need it later (or want to stick with dynamic sql with this one), instead of using the nested Iif's, you coudl try a switch statement. There's an "in-line" version (see e.g. below) that seems to work a treat. I haven't got a link for the official definition, but it's basically switch (<case1>, <statement_if_case1_is_true>, <case2>, <statement_if_case2_is_true>, <casen>, <do_stuff>,.....)

=switch(Parameters!Parm1.Value <> 'bob', "SELECT * FROM myTable WHERE Col1 = " & Parameters!Parm1.Value, Parameters!Parm1.Value <> 'something_else', "SELECT * FROM Some_Other_Table")

Steve.