asked on

SQL Query

Hi,

Im new to SQL, done a 2 day course so barley touched the thing, now Reporting services is going in to my company, and im struggling abit.

We have SQL Server 2008

I want to be able to add a parameter in my query so that when i run a report it will let me select by employee. heres my query so far ( using the Standard TSQL2012 db)

"use TSQL2012
go

select so.orderid as OrderID
      ,emp.firstname as [Employee Name]
      ,sc.companyname as Customer
      ,so.orderdate as OrderDate
      ,pp.productname as Product
      ,pp.unitprice as Price
      ,sod.qty as Quantity
      ,pp.unitprice * sod.qty as Total
      ,so.shipname as [Delivery Name]
      ,so.shipaddress as [Address Line 1]
      ,so.shipcity as City
      ,so.shipregion as Region
      ,so.shippostalcode as Postcode
      ,so.shipcountry as Country

from Sales.Orders as so
      left join Sales.OrderDetails as sod on so.orderid = sod.orderid
            left join HR.Employees as emp on emp.empid = so.empid
                  left join Sales.Customers as sc on sc.custid = so.custid
                        left join Production.Products as pp on pp.productid = sod.productid
where emp.firstname = @empname
      Or @empname = '[All Employees]' "

When i put my where clause in, it throws an error at me

"Msg 137, Level 15, State 2, Line 22
Must declare the scalar variable "@firstname" "

Thanks
Luke

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xObIA

ASKER

Hi All Thanks for all you advice.

if Declare @empname varchar(50) --(firstname was a typo)
SET @empname = 'Bucky'

Will this still pull through all employees though?? Because when i execute the query, it only pulls data for 'Bucky'.

As mentioned before, im using reporting services, what ive done is created an additional dataset called 'Employees' with this statement in, but preferably this isnt what i want to do

"select distinct
EMP.firstname

from HR.Employees as emp

Union
Select '[All Employees]'"

and just used

"where emp.firstname = @empname or @empname = '[All Employees]'"

at the end of my statement.

Thanks Guys

PortletPaul

>>"Will this still pull through all employees though??"
Not by itself

if you set a parameter to 'Bucky', yes it will limit the result to rows with 'Bucky'
Isn't that what you want?

What you describe above I think is workable (but I'm reading between lines), it should give you all records if no specific name is chosen, but if a specific name is chosen you only get that.

--
another tip:

use UNION ALL
not just "union"

select distinct
EMP.firstname, 1 as sortby
from HR.Employees as emp

Union ALL

Select '[All Employees]', 0

ORDER BY sortby, firstname

A "union" by itself will undertake more work than "union all" because "union" produces unique rows, "union all" doesn't care. With "union" after combining the lists of data that whole combination is evaluated for repetition (which is "more work").

If you know that the 2 lists cannot produce repetition, use UNION ALL

PortletPaul

Happy to have helped solve the question. It is however a unsettling the you chose the Grade as B; that indicates I left something unsolved or unattended or unexplained.

What was deficient in the solution?