How to use only 1 query for a 1 or all query with MSSQL
- If often have queries that identical except for the user parameter.
- Users sometimes want all the data for the month.
- Users sometimes want the data for the month just for one employee.
- The standard way is just to have 2 separate queries, but that means to have 2 queries to maintain, when it's only 1 query but 2 different user input.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Data used
- USE sql911;
- go
- SELECT sales_date, sales_qty, sales_price, emp_short
- FROM sales;
- go
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 2005-12-22 10:01:01.000 10.00 11.75 000310 2006-10-06 10:01:01.000 10.00 11.75 000310 2005-11-30 10:01:01.000 0.00 11.75 000110 2006-10-06 10:01:01.000 10.00 11.75 000310 2005-12-06 11:01:01.000 8.00 11.75 000010 2006-02-28 13:01:01.000 0.00 NULL 000190 2006-03-19 14:01:01.000 5.00 11.75 000110 2006-10-06 10:01:01.000 10.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000190 2006-04-20 15:01:01.000 4.00 11.75 000030 2006-03-01 16:01:01.000 3.00 11.75 000010 2004-10-06 10:01:01.000 0.00 NULL 000310 2005-11-21 10:01:01.000 9.00 11.75 000010 2006-03-31 17:01:01.000 0.00 11.75 000190 2006-01-22 12:01:01.000 7.00 11.75 000310 2006-02-23 13:01:01.000 6.00 11.75 000110 2005-03-25 14:01:01.000 5.00 11.75 000190 2006-10-31 10:01:01.000 10.00 11.75 000110 2006-10-29 10:01:01.000 3.00 11.75 000110 2007-06-17 17:01:01.000 2.00 11.75 000010 2007-04-18 00:00:00.000 0.00 11.75 000010 2007-05-31 00:00:00.000 5.00 11.75 000030 2007-07-06 00:00:00.000 1.00 11.75 000190 2007-05-03 09:00:00.000 3.00 11.75 000010 2007-05-05 04:00:00.000 1.00 11.75 000010 2006-10-06 10:01:01.000 10.00 11.75 000030 2004-03-06 14:01:01.000 0.00 NULL 000110 2005-04-06 15:01:01.000 4.00 11.75 000010 2005-05-06 16:01:01.000 3.00 11.75 000190 2006-06-06 17:01:01.000 2.00 11.75 000030 2006-07-06 19:01:01.000 1.00 11.75 000310 2006-03-06 14:01:01.000 5.00 11.75 000310 2006-04-06 15:01:01.000 4.00 11.75 000010 2007-05-07 11:00:00.000 2.00 11.75 000010 2006-06-06 17:01:01.000 0.00 NULL 000110 2006-07-06 19:01:01.000 1.00 11.75 000190 2005-10-06 10:01:01.000 10.00 11.75 000030 2007-05-08 15:00:00.000 1.50 11.75 000010 2005-12-06 11:01:01.000 8.00 11.75 000190 2006-01-06 12:01:01.000 7.00 11.75 000030 2007-05-07 11:30:00.000 2.00 10.50 000030 2007-05-08 13:30:00.000 1.00 10.00 000030 2006-07-06 19:01:01.000 1.00 11.75 000010 2007-05-09 14:00:00.000 1.25 10.00 000110 2007-11-06 10:01:01.000 9.00 11.75 000030 2007-05-10 14:00:00.000 5.00 10.00 000190 2007-05-11 14:15:00.000 0.00 10.00 000190 2007-05-12 15:00:00.000 0.00 10.00 000190 2007-05-13 16:00:00.000 1.00 10.00 000190 2007-05-08 05:00:00.000 1.00 10.00 000310 2007-05-07 00:00:00.000 2.00 10.00 000310 2007-05-14 11:00:00.000 3.00 10.00 000310 2006-07-06 00:00:00.000 1.00 11.75 000190 2007-05-11 13:00:00.000 3.50 10.00 000310 2007-05-08 05:00:00.000 0.00 10.00 000010 2007-05-08 07:00:00.000 2.00 10.00 000010 2007-05-08 11:00:00.000 3.00 10.00 000010 (57 row(s) affected)
Query for 1 or for all
First, the query for all
- DECLARE @emp CHAR(6)
- SET @emp = null
- SELECT sales_date, sales_qty, sales_price, emp_short
- FROM sales
- WHERE emp_short = COALESCE(@emp, emp_short);
- go
- Line 1: declare @emp char(6): Creates the variable @emp
- Line 2: set @emp = null: we want all the sales
- Line 5: emp_short = coalesce(@emp, emp_short): if @emp is null, this line becomes: emp_short = emp_short which is always true, so we get all the sales.
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 2005-12-22 10:01:01.000 10.00 11.75 000310 2006-10-06 10:01:01.000 10.00 11.75 000310 2005-11-30 10:01:01.000 0.00 11.75 000110 2006-10-06 10:01:01.000 10.00 11.75 000310 2005-12-06 11:01:01.000 8.00 11.75 000010 2006-02-28 13:01:01.000 0.00 NULL 000190 2006-03-19 14:01:01.000 5.00 11.75 000110 2006-10-06 10:01:01.000 10.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000190 2006-04-20 15:01:01.000 4.00 11.75 000030 2006-03-01 16:01:01.000 3.00 11.75 000010 2004-10-06 10:01:01.000 0.00 NULL 000310 2005-11-21 10:01:01.000 9.00 11.75 000010 2006-03-31 17:01:01.000 0.00 11.75 000190 2006-01-22 12:01:01.000 7.00 11.75 000310 2006-02-23 13:01:01.000 6.00 11.75 000110 2005-03-25 14:01:01.000 5.00 11.75 000190 2006-10-31 10:01:01.000 10.00 11.75 000110 2006-10-29 10:01:01.000 3.00 11.75 000110 2007-06-17 17:01:01.000 2.00 11.75 000010 2007-04-18 00:00:00.000 0.00 11.75 000010 2007-05-31 00:00:00.000 5.00 11.75 000030 2007-07-06 00:00:00.000 1.00 11.75 000190 2007-05-03 09:00:00.000 3.00 11.75 000010 2007-05-05 04:00:00.000 1.00 11.75 000010 2006-10-06 10:01:01.000 10.00 11.75 000030 2004-03-06 14:01:01.000 0.00 NULL 000110 2005-04-06 15:01:01.000 4.00 11.75 000010 2005-05-06 16:01:01.000 3.00 11.75 000190 2006-06-06 17:01:01.000 2.00 11.75 000030 2006-07-06 19:01:01.000 1.00 11.75 000310 2006-03-06 14:01:01.000 5.00 11.75 000310 2006-04-06 15:01:01.000 4.00 11.75 000010 2007-05-07 11:00:00.000 2.00 11.75 000010 2006-06-06 17:01:01.000 0.00 NULL 000110 2006-07-06 19:01:01.000 1.00 11.75 000190 2005-10-06 10:01:01.000 10.00 11.75 000030 2007-05-08 15:00:00.000 1.50 11.75 000010 2005-12-06 11:01:01.000 8.00 11.75 000190 2006-01-06 12:01:01.000 7.00 11.75 000030 2007-05-07 11:30:00.000 2.00 10.50 000030 2007-05-08 13:30:00.000 1.00 10.00 000030 2006-07-06 19:01:01.000 1.00 11.75 000010 2007-05-09 14:00:00.000 1.25 10.00 000110 2007-11-06 10:01:01.000 9.00 11.75 000030 2007-05-10 14:00:00.000 5.00 10.00 000190 2007-05-11 14:15:00.000 0.00 10.00 000190 2007-05-12 15:00:00.000 0.00 10.00 000190 2007-05-13 16:00:00.000 1.00 10.00 000190 2007-05-08 05:00:00.000 1.00 10.00 000310 2007-05-07 00:00:00.000 2.00 10.00 000310 2007-05-14 11:00:00.000 3.00 10.00 000310 2006-07-06 00:00:00.000 1.00 11.75 000190 2007-05-11 13:00:00.000 3.50 10.00 000310 2007-05-08 05:00:00.000 0.00 10.00 000010 2007-05-08 07:00:00.000 2.00 10.00 000010 2007-05-08 11:00:00.000 3.00 10.00 000010 (57 row(s) affected)
Second, the query for one employee
- DECLARE @emp CHAR(6)
- SET @emp = '000310'
- SELECT sales_date, sales_qty, sales_price, emp_short
- FROM sales
- WHERE emp_short = COALESCE(@emp, emp_short);
- go
- Line 2: set @emp = '000310' : we want the sales only for employee '000310'
- Line 5: emp_short = coalesce(@emp, emp_short): if @emp is is NOT null, this line becomes: emp_short = '000310' which is what we wanted.
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 2005-12-22 10:01:01.000 10.00 11.75 000310 2006-10-06 10:01:01.000 10.00 11.75 000310 2006-10-06 10:01:01.000 10.00 11.75 000310 2004-10-06 10:01:01.000 0.00 NULL 000310 2006-01-22 12:01:01.000 7.00 11.75 000310 2006-07-06 19:01:01.000 1.00 11.75 000310 2006-03-06 14:01:01.000 5.00 11.75 000310 2007-05-08 05:00:00.000 1.00 10.00 000310 2007-05-07 00:00:00.000 2.00 10.00 000310 2007-05-14 11:00:00.000 3.00 10.00 000310 2007-05-11 13:00:00.000 3.50 10.00 000310 (11 row(s) affected)