Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query

Posted on 2014-04-08
6
Medium Priority
?
471 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 375 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 1125 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 1125 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

730 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