SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jsph0508
Starting Member

3 Posts

Posted - 04/19/2013 :  09:02:33  Show Profile  Reply with Quote
Hi everyone - I'm new to SQL and am having difficulty with this problem. Any guidance/suggestions you can offer, would be greatly appreciated!


Create and test a stored procedure named sp_quest10 that accepts 3 parameters:

1. start date (required)
2. end date (required)
3. state (optional)

The sp should print to the screen the invoices (invid, invdate, invtotal) that match the specified criteria.  So, if the start date is 3/1/2012 and the end date is 3/31/2012 then it should show all invoices between that range.  If the user specifies a state, make sure the data set only shows invoices in the date range from vendors in that state, otherwise shows all invoices within the range regardless of state.

In addition, print a total at the bottom that is the sum of all shown invoicetotals.

If a required parameter is not provided, print an appropriate error message.

Test the sp with correct and incorrect parameter values.

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 04/19/2013 :  09:16:14  Show Profile  Reply with Quote
I think this is assignment for you.. you have to try atleast once..
Show us what you have tried and tell us the difficulty where you got issue


--
Chandu
Go to Top of Page

jsph0508
Starting Member

3 Posts

Posted - 04/19/2013 :  09:28:00  Show Profile  Reply with Quote
Oh sorry, I should have included it. This is what I've tried:

USE AP
IF OBJECT_ID ('sp_quest10') IS NOT NULL
DROP PROC sp_quest10
GO

CREATE PROC sp_quest10
@StartDate smalldatetime,
@EndDate smalldatetime,
@State varchar (20) = NULL,

GO

AS
IF @State IS NOT NULL

SELECT InvID, InvDate, InvTotal
FROM Invoices JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID

WHERE (InvDate BETWEEN @StartDate AND @EndDate)
AND (VendorState = @State)

ELSE
SELECT InvID, InvDate, InvTotal
FROM Invoices
WHERE (InvDate > @StartDate) AND (InvDate < @EndDate)



EXEC sp_quest10

DECLARE @StartDate '2008-01-01', @EndDate '2009-12-31', @State CA

Receiving these error messages:

Msg 102, Level 15, State 1, Procedure sp_quest10, Line 5
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@State".
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@StartDate".
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable "@StartDate".

Thank you!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 04/19/2013 :  09:30:39  Show Profile  Reply with Quote
-- Remove the red marked part
USE AP
IF OBJECT_ID ('sp_quest10') IS NOT NULL
DROP PROC sp_quest10
GO

CREATE PROC sp_quest10
@StartDate smalldatetime,
@EndDate smalldatetime,
@State varchar (20) = NULL
,

GO


AS
IF @State IS NOT NULL

SELECT InvID, InvDate, InvTotal
FROM Invoices JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID

WHERE (InvDate BETWEEN @StartDate AND @EndDate)
AND (VendorState = @State)

ELSE
SELECT InvID, InvDate, InvTotal
FROM Invoices
WHERE (InvDate >= @StartDate) AND (InvDate <= @EndDate)



EXEC sp_quest10

DECLARE @StartDate '2008-01-01', @EndDate '2009-12-31', @State CA



--
Chandu

Edited by - bandi on 04/19/2013 09:31:16
Go to Top of Page

jsph0508
Starting Member

3 Posts

Posted - 04/19/2013 :  10:15:25  Show Profile  Reply with Quote
Thank you. I'm still getting the following error message:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '2008-01-01'.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/19/2013 :  10:37:15  Show Profile  Reply with Quote
you need to execute the stored procedure in following manner ..

EXEC sp_quest10 @StartDate='???',@endDate='????',@State='??'



Cheers
MIK
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 04/22/2013 :  01:39:10  Show Profile  Reply with Quote
--Alterante is:
DECLARE @StartDate ='2008-01-01', @EndDate ='2009-12-31', @State='CA'
EXEC sp_quest10 @StartDate, @EndDate, @State

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000