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.
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. Thestored procedure will accept two parameters, the beginning date and the ending date. Both of these dates willrelate 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 theuser telling them NULL values are not allowedThe stored procedure should display the Shippeddate, OrderID, ShipCitry and Region columns for any recordswithin 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)ASset @begindate = '19960710'set @enddate = '19980506'IF @begindate IS NULL Begin Raiserror ('Null values are not allowed', 6,0) Endelse IF @enddate IS NULL Begin Raiserror ('Null values are not allowed', 6,0) EndIf @begindate < @enddateSelect Shippeddate, OrderID, Shipcity, shipregion from Orderswhere shippeddate between @begindate and @enddateGOexec 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 variable2. Why are you setting the value of @begindate & @enddate in your stored procedure when you are passing in as a variable ? KH |
|
|
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. Thestored procedure will accept two parameters, the beginning date and the ending date. Both of these dates willrelate 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 theuser telling them NULL values are not allowedThe stored procedure should display the Shippeddate, OrderID, ShipCitry and Region columns for any recordswithin 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)ASset @begindate = '19960710'set @enddate = '19980506'IF @begindate IS NULL Begin Raiserror ('Null values are not allowed', 6,0) Endelse IF @enddate IS NULL Begin Raiserror ('Null values are not allowed', 6,0) EndIf @begindate < @enddateSelect Shippeddate, OrderID, Shipcity, shipregion from Orderswhere shippeddate between @begindate and @enddateGOexec yeartoyearsales @begindate='', @enddate='19961001'(give me a result of 809 rows. Your input is appreciated)
--------------------keeping it simple... |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-26 : 01:13:38
|
Always use Proper DateTime datatype. Otherwise you may get wrong resultsMadhivananFailing to plan is Planning to fail |
|
|
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. |
|
|
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 thisSELECT OrderId, sum((unitprice *quantity)-disc) . . .FROM Orders inner join OrdersDetailsON . . . WHERE . . .GROUP BY . . . KH |
|
|
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 confusingit 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 OrdersDetailsON . . . WHERE . . .GROUP BY .would this be the right solution to the above mentioned question? |
|
|
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 thisfor example, your stored procedure (SP) suppose to returns / lists the customer with name begining with A.select cust_no, cust_name, cust_addrfrom custwhere 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) OUTPUTasbegin 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 |
|
|
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 15Ambiguous column name 'OrderID'.create proc YeartoYearSales@begindate smalldatetime,@enddate smalldatetimeASIF @begindate IS NULL Begin Raiserror ('Null values are not allowed', 6,0) EndIF @enddate IS NULL Begin Raiserror ('Null values are not allowed', 6,0) EndIf @begindate < @enddateSelect Shippeddate,OrderID, Shipcity, shipregion, sum((UnitPrice*Quantity)-Discount) as "Sales" from Orders ainner join "Order Details" b on a.OrderID=b.OrderID where shippeddate between @begindate and @enddateGO |
|
|
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 aliasSelect Shippeddate, a.OrderIDorSelect Shippeddate, b.OrderID as a.OrderID is b.OrderID you can use anyone. KH |
|
|
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 ASIF @begindate IS NULL Begin Raiserror ('Null values are not allowed', 16,1) Return EndElse IF @enddate IS NULL Begin Raiserror ('Null values are not allowed', 16,1) Return EndIf @begindate < @enddateSelect Shippeddate,a.OrderID, Shipcity, shipregion, ((UnitPrice*Quantity)-Discount) as "Sales" from Orders ainner join "Order Details" b on (a.OrderID=b.OrderID) where shippeddate between @begindate and @enddateGOexec yeartoyearsales @begindate='', @enddate=''command completed successfully |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-04-26 : 23:32:01
|
empty string is not NULL. NULL is not empty stringWhen you pass empty string to a dateime, it gets converts to 1900-01-01Try this :declare @begindate datetimeselect @begindate = ''select @begindate-- RESULT : -- 1900-01-01 00:00:00.000 KH |
|
|
|
|
|
|
|