Stored procedure - SQL Server Forums

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 procedure

Author

Topic

jsph0508
Starting Member

3 Posts

Posted - 2013-04-19 : 09:02:33

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-19 : 09:16:14

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

jsph0508
Starting Member

3 Posts

Posted - 2013-04-19 : 09:28:00

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!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-19 : 09:30:39

-- 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

jsph0508
Starting Member

3 Posts

Posted - 2013-04-19 : 10:15:25

Thank you. I'm still getting the following error message:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '2008-01-01'.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-19 : 10:37:15

you need to execute the stored procedure in following manner ..

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

Cheers
MIK

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-22 : 01:39:10

--Alterante is:
DECLARE @StartDate ='2008-01-01', @EndDate ='2009-12-31', @State='CA'
EXEC sp_quest10 @StartDate, @EndDate, @State

--
Chandu