Solved

SQL Query

Posted on 2014-04-08
6
451 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

735 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