Information générale
Titre:
SQL Server procedure, for filtering records in a time range
Dear all,
A stored procedure is written on SQL server to port the data in the range specified. The procedure takes two arguments, iyear nd imonth, which represents the year and month used for filtering out the orders in the month. However, each time I run the stored procedure, errors were reported, showing that there are errors near the argument I passed. I don't know what is going on.
Following is the piece of code I did. I will appreciate any comments, and suggestion. Many thanks.
if exists (select * from sysobjects where id = object_id('dbo.getoverall') and sysstat & 0xf = 4)
drop procedure dbo.getoverall
GO
CREATE PROCEDURE getoverall (@iyear integer,@imonth integer) AS
/* Declare working variables */
declare @cperson char(10),
@ipv integer,
@igv integer,
@iov integer,
@ibonus integer
/* turn off resulting counting */
set nocount on
/* declare the main cursor overall c_overall */
declare c_overall cursor for
select ord_p, sum(money) pv, 0 gv, 0 ov from orders where datepart(year,ord_date)=@iyear and datepart(month,ord_date)=@imonth group by ord_p order by ord_p
/* open the cursor for fetching */
open c_overall
/* fetching the first records into variables defined */
fetch c_overall into @cperson, @ipv, @igv, @iov
/* repeating processing while not end of cursor */
While @@fetch_status=0
Begin
fetch c_overall into @cperson, @ipv, @igv, @iov
End
close c_overall
deallocate c_overall
return 0
GO
getoverall(1997,11)
go
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement