CODEWITHSUNDEEP
t-sqlsql-server-2005optional-parameters
Misiu
Misiu

Reputation: 4919

TSQL 2 optional parameters

I have a simple query like so:

SELECT * FROM orders

that returns all orders, simple.

I would like to add optional parameters to my query.

DECLARE @group INT;
SET @group = 1 --IT1

SELECT * FROM orders WHERE group = isnull(@group, group)

This also works fine.

My problem is that i need a second parameter like so:

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)

My orders table has column GROUP, but no column DEPARTMENT.

Situation looks like so: I have 2 comboboxes. One for departments, second for group. In first I have options:

Second is populated when user select something from combo1. If he selects IT the group combo box will have options:

If user select from first combo department and then group it is easy because I then check only group.
Problem is when he wants all orders from department IT.

Here is how groups are assigned to departments

Any ideas?

ADDED:

I have created something like that:

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)

I don't know if it is corrent, it somehow works :)

Upvotes: 2

Views: 206

Answers (2)

Eugene
Eugene

Reputation: 2985

Here is the query. In the front end, when a single group is selected, you assign the @group variable, just like you did before. When no single group is selected, you assign the flag for department. So if all IT was selected you set @allIT to 1 (or whatever, as long as it is not NULL) and proceed like so:

SELECT * FROM orders 
WHERE (@group = NULL OR group = @group)
AND
(@allIT = NULL OR group = 1 or group = 2 or group = 3)
AND
(@allCallCenter = NULL OR group = 4 or group = 5)
...

If nothing is selected in a combobox, set that parameter value to NULL.

Upvotes: 1

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

You might pretend you have a table by using CTE populated with records via union all. Then perform a join and a check. If you prefer CTE might be replaced with derived table.

; WITH departments (DepartmentID, GroupID) as (
  select 'IT', 1
  union all
  select 'IT', 2
  union all
  select 'IT', 5
  union all
  select 'CALL CENTRE', 4
  union all
  select 'CALL CENTRE', 6
  union all
  select 'PRINT', 3
)
SELECT * 
FROM orders 
  INNER JOIN departments
     ON orders.[group] = departments.GroupID
WHERE (@Group is null OR [group] = @group)
  AND (@department is null OR DepartmentID = @department)

Upvotes: 1

Related Questions