Solved

SQL Query

Posted on 2014-04-08
6
449 Views
Last Modified: 2014-04-17
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
0
Comment
Question by:xObIA
  • 4
6 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 125 total points
ID: 39986591
>I want to be able to add a parameter in my query
If you're executing this manually, then before your SELECT add something like this...
Declare @firstname varchar(50) = 'Bucky'

Open in new window

btw The error message shows @firstname, but the code only shows @empname.  Typo?

To make this code executable via any automated process, and accept the parameter, you'll have to create a Stored procedure out of it.
CREATE PROC name_me(@firstname varchar(50)) AS
-- Your SQL statement here
GO

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 375 total points
ID: 39987876
Must declare the scalar variable "@firstname"

simply means an @name has been used - but before using it, you must "declare" it

(i.e. it does not exist until it is declared)

ps: Jim has used a shorthand syntax above, a declaration is just this bit

DECLARE @firstname varchar(50)

-- now give it a value
SET @firstname  = 'Bucky'

-- now you can use it
select * from sometable where somefield = @firstname

see:
http://technet.microsoft.com/en-us/library/ms188927.aspx
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 375 total points
ID: 39987906
while I'm here, nice to see you using good formatting in your query already

Just one tip. When defining join conditions "be consistent" with how you write-up he conditions. e.g.

from Sales.Orders as so
      left join Sales.OrderDetails as sod on so.orderid = sod.orderid
         -- here the 'prior table' (so) is referenced first

-- but from here though it's the reverse
            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


I'm a believer in 'prior table' first, so I'd write the joins like so:

from Sales.Orders as so
      left join Sales.OrderDetails as sod on so.orderid = sod.orderid
      left join HR.Employees as emp on so.empid = emp.empid
      left join Sales.Customers as sc on so.custid = sc.custid
      left join Production.Products as pp on sod.productid = pp.productid
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:xObIA
ID: 39988083
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39988204
>>"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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40006669
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?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question