Solved

SQL Query

Posted on 2014-04-08
6
460 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 66

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 49

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 49

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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 49

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 49

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

623 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