Solved

SQL Query

Posted on 2014-04-08
6
441 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Update in Sql 7 30
Join vs where 2 37
How to insert gas or chemical names in SQL server. For e.g. CO2 to CO₂. 5 67
all records from previous month 6 45
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
In this article I will describe the Copy Database Wizard 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.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

930 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now