Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored Proc

Author  Topic 

michelleNoby
Starting Member

12 Posts

Posted - 2006-04-25 : 22:26:30
PROBLEM:
Using the Northwinds database and the Orders table to create a stored procedure called YearToYearSales. The
stored procedure will accept two parameters, the beginning date and the ending date. Both of these dates will
relate to the Shippeddate column of the Orders table.
The stored procedure is return all sales between the range of the two dates specified when the query is executed.
Use the RAISERROR condition to trap an error if either of the specified dates is NULL. Return a message to the
user telling them NULL values are not allowed
The stored procedure should display the Shippeddate, OrderID, ShipCitry and Region columns for any records
within the two specified dates. The beginning date must be less than the ending date.


SOLUTION (NOT WORKING):

create proc YeartoYearSales
@begindate varchar(15),
@enddate varchar (20)
AS
set @begindate = '19960710'
set @enddate = '19980506'
IF @begindate IS NULL
Begin
Raiserror ('Null values are not allowed', 6,0)
End
else IF @enddate IS NULL
Begin
Raiserror ('Null values are not allowed', 6,0)
End

If @begindate < @enddate
Select Shippeddate, OrderID, Shipcity, shipregion from Orders
where shippeddate between @begindate and @enddate
GO

exec yeartoyearsales @begindate='', @enddate='19961001'

(give me a result of 809 rows. Your input is appreciated)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-25 : 22:34:52
1. Use datatime datatype for your begin and end date variable
2. Why are you setting the value of @begindate & @enddate in your stored procedure when you are passing in as a variable ?




KH


Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-25 : 22:54:14
quote:
Originally posted by michelleNoby

PROBLEM:
Using the Northwinds database and the Orders table to create a stored procedure called YearToYearSales. The
stored procedure will accept two parameters, the beginning date and the ending date. Both of these dates will
relate to the Shippeddate column of the Orders table.
The stored procedure is return all sales between the range of the two dates specified when the query is executed.
Use the RAISERROR condition to trap an error if either of the specified dates is NULL. Return a message to the
user telling them NULL values are not allowed
The stored procedure should display the Shippeddate, OrderID, ShipCitry and Region columns for any records
within the two specified dates. The beginning date must be less than the ending date.


SOLUTION (NOT WORKING):

create proc YeartoYearSales
@begindate varchar(15),
@enddate varchar (20)
AS
set @begindate = '19960710'
set @enddate = '19980506'
IF @begindate IS NULL
Begin
Raiserror ('Null values are not allowed', 6,0)
End
else IF @enddate IS NULL
Begin
Raiserror ('Null values are not allowed', 6,0)
End

If @begindate < @enddate
Select Shippeddate, OrderID, Shipcity, shipregion from Orders
where shippeddate between @begindate and @enddate
GO

exec yeartoyearsales @begindate='', @enddate='19961001'

(give me a result of 809 rows. Your input is appreciated)





--------------------
keeping it simple...
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-26 : 01:13:38
Always use Proper DateTime datatype. Otherwise you may get wrong results

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

michelleNoby
Starting Member

12 Posts

Posted - 2006-04-26 : 08:51:26
thanks for the advice.
"...The stored procedure is return all sales between the range of the two dates specified when the query is executed."
how would i return the value of all sales between @begin and @enddate? should i use a select statement joining the orders and orderdetails tables to get the sales? sum((unitprice *quantity)-disc)? how would i incorporate this inside the sp? Or should i do the select statement with joins when im executing the sp? Ideas would be helpful.
Thanks.



Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-26 : 10:05:19
"should i use a select statement joining the orders and orderdetails tables to get the sales?"
Yes. You should use INNER JOIN to join the Orders table and OrderDetails table

"sum((unitprice *quantity)-disc)? how would i incorporate this inside the sp? "
just add the statement to part of the select.
something like this

SELECT OrderId, sum((unitprice *quantity)-disc) . . .
FROM Orders inner join OrdersDetails
ON . . .
WHERE . . .
GROUP BY . . .




KH


Go to Top of Page

michelleNoby
Starting Member

12 Posts

Posted - 2006-04-26 : 10:39:35
^
"...The stored procedure is return all sales between the range of the two dates specified when the query is executed."

should i do the select...joins when i execute the sp? The keyword here is RETURN, right? Its confusing

it should look like this:

If (select shippeddate from orders where shippeddate between @begindate and @enddate)
/*then do this??*/
SELECT OrderId, sum((unitprice *quantity)-disc) . . .
FROM Orders inner join OrdersDetails
ON . . .
WHERE . . .
GROUP BY .


would this be the right solution to the above mentioned question?

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-26 : 10:57:29
"The keyword here is RETURN, right? Its confusing"
Depends on how much data you need to retrieve and how do you want it back. There are several ways.
If your store procedure is going to return a result sets (multiple records with multiple column per records) you basically just do this
for example, your stored procedure (SP) suppose to returns / lists the customer with name begining with A.
select cust_no, cust_name, cust_addr
from cust
where cust_name like 'A%'

The above query might return more than one records and multiple columns of information.

If your SP is going to calculate the order amount with the given order_id. In this case, the SP will just return one value which is the Order Amount. Typically you will pass the order_id to the SP as a parameter. The paremeter passing can be both way. IN and OUT. So you can also use parameter to pass the result of your calculation out.
Example :

create procedure your_sp_name
@orderid int,
@orderamt decimal(10,2) OUTPUT
as
begin
select @orderamt = sum( . . .)
from order inner join orderdetails
on . . .
where orderid = @orderid
and . . .
end


Give it a try. Don't try to code all you need at once you might encounter too many error and might be difficult for you to debug. Add code to your SP bit by bit.
Good Luck



KH


Go to Top of Page

michelleNoby
Starting Member

12 Posts

Posted - 2006-04-26 : 21:19:04
---here's what i came up with, but i get an error when i try to create it:
Server: Msg 209, Level 16, State 1, Procedure YeartoYearSales, Line 15
Ambiguous column name 'OrderID'.


create proc YeartoYearSales
@begindate smalldatetime,
@enddate smalldatetime
AS
IF @begindate IS NULL
Begin
Raiserror ('Null values are not allowed', 6,0)
End
IF @enddate IS NULL
Begin
Raiserror ('Null values are not allowed', 6,0)
End

If @begindate < @enddate
Select Shippeddate,OrderID, Shipcity, shipregion, sum((UnitPrice*Quantity)-Discount)
as "Sales" from Orders a
inner join "Order Details" b on a.OrderID=b.OrderID
where shippeddate between @begindate and @enddate
GO

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-26 : 22:04:17
"Ambiguous column name 'OrderID'."
as there are more than one table with the same column name, you have to specify which OrderID that you want.
prefix the column name with the table name or table alias

Select Shippeddate, a.OrderID
or
Select Shippeddate, b.OrderID

as a.OrderID is b.OrderID you can use anyone.



KH


Go to Top of Page

michelleNoby
Starting Member

12 Posts

Posted - 2006-04-26 : 23:28:19
^
i got the above done, it works only when i put a range of dates that are in my sets of data. when i exec with null values, it skips the if clauses to trap errors.

alter proc YeartoYearSales
@begindate smalldatetime,
@enddate smalldatetime
AS

IF @begindate IS NULL
Begin
Raiserror ('Null values are not allowed', 16,1)
Return
End
Else IF @enddate IS NULL
Begin
Raiserror ('Null values are not allowed', 16,1)
Return
End
If @begindate < @enddate
Select Shippeddate,a.OrderID, Shipcity, shipregion, ((UnitPrice*Quantity)-Discount)
as "Sales" from Orders a
inner join "Order Details" b on (a.OrderID=b.OrderID)
where shippeddate between @begindate and @enddate

GO

exec yeartoyearsales @begindate='', @enddate=''
command completed successfully


Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-26 : 23:32:01
empty string is not NULL. NULL is not empty string

When you pass empty string to a dateime, it gets converts to 1900-01-01
Try this :

declare @begindate datetime

select @begindate = ''
select @begindate

-- RESULT :
-- 1900-01-01 00:00:00.000




KH


Go to Top of Page
   

- Advertisement -