StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTSQL 2 optional parameters
text
Body
copied!<p>I have a simple query like so:</p> <pre><code>SELECT * FROM orders </code></pre> <p>that returns all orders, simple.</p> <p>I would like to add optional parameters to my query.</p> <pre><code>DECLARE @group INT; SET @group = 1 --IT1 SELECT * FROM orders WHERE group = isnull(@group, group) </code></pre> <p>This also works fine.</p> <p>My problem is that i need a second parameter like so:</p> <pre><code>DECLARE @group INT; SET @group = 1 --IT1 DECLARE @department VARCHAR(8); SET @department = 'IT' SELECT * FROM orders WHERE /*and here add limitation with department*/ group = isnull(@group, group) </code></pre> <p>My orders table has column GROUP, but no column DEPARTMENT.</p> <p>Situation looks like so: I have 2 comboboxes. One for departments, second for group. In first I have options:</p> <ul> <li>All</li> <li>IT</li> <li>CALL CENTRE</li> </ul> <p>Second is populated when user select something from combo1. If he selects IT the group combo box will have options:</p> <ul> <li>All</li> <li>IT1</li> <li>IT2</li> </ul> <p>If user select from first combo department and then group it is easy because I then check only group.<br> Problem is when he wants all orders from department IT.</p> <p>Here is how groups are assigned to departments</p> <ul> <li>IT - 1,2,5</li> <li>CALL CENTRE - 4,6</li> <li>PRINT - 3</li> </ul> <p>Any ideas?</p> <p>ADDED:</p> <p>I have created something like that:</p> <pre><code>DECLARE @group INT; SET @group = 1 --IT1 DECLARE @department VARCHAR(8); SET @department = 'IT' SELECT * FROM orders WHERE (@department IS NULL OR (@department='IT' AND group IN (1,2,5)) OR (@department='PRINT' AND group =3)) AND (@group IS NULL OR group = @group) </code></pre> <p>I don't know if it is corrent, it somehow works :)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload