Stored Procedures! Help 1

[TheMikeh]

Hey,

I have a database that holds a list of figures combined with a open date and an end date. I need to grab a SUM of all the figures whereby the end date falls within a set date range.

I need this as a stored procedure where by I can pass the userid in 'foreign key' and 2 dates to get a sum of all the figures that falls in this. Currently my procedure looks like (I know its wrong) might give you an idea what I am after

Help me please!

The database is called goldmine, the table is called opmgr.


--------------------------------------------
CREATE PROCEDURE dbo.MikeProfitCheck ( @userID nvarchar, @BDate DateTime, @EDate DateTime)
AS
DECLARE @amount int;
SELECT @amount=sum(Opmgr.closeamt)
FROM Opmgr
WHERE
Opmgr.userid = @userID
AND
Opmgr.status='won'
AND Opmgr.closeddate BETWEEN @BDate AND @EDate
RETURN @amount
GO
--------------------------------------------


Thanks in advance!
Mike

 

[TheMikeh]

Hi,

Ok no worries after a few hours found my silly little mistake! I wasnt declaring the username correctly, silly me! The final code Im using in case anyone is interested is


------------------------------------------------------------
CREATE PROCEDURE dbo.GrossProfitGenerated ( @userID nvarchar(20), @Beginning_Date DateTime, @Ending_Date DateTime)

AS

SELECT Amount=sum(Opmgr.closeamt)
FROM Opmgr
WHERE
Opmgr.userid = @userID
AND
Opmgr.status='won'
AND Opmgr.closeddate BETWEEN @Beginning_Date AND @Ending_Date

RETURN
GO
-----------------------------------------------------------

 

[monksnake]

I see maybe a few problems, I'll highlight and I do see one thing you should do though to speed up your query by making the column closeddate parseable (got this from George):

CREATE PROCEDURE dbo.MikeProfitCheck (@userID nvarchar, @BDate DateTime, @EDate DateTime)
AS
DECLARE @amount int[!];[/!]
SELECT  @amount=sum(Opmgr.closeamt) 
FROM  Opmgr
WHERE 
Opmgr.userid = @userID
AND Opmgr.status='won'
[!]AND Opmgr.closeddate >= @BDate 
AND Opmgr.closeddate <= @EDate[/!]
RETURN @amount
GO

<.

 

[gmmastros]

Actually, monksnake... I think you meant sargable, not paresable.

The between statement is sargable (and parsable). Mike, what monksnake is referring to is the ability of the SQL Engine to make use of indexes. Sometimes where clauses are written so that the SQL Engine must do a table/index scan in order to pick up the proper records. By having an index, and re-writing the where clause, often times you can get an index seek instead of a scan, which is better for performance.

Using the between operator will cause an index seek (if an index exists).

If your closeddate column includes time, then you may not be getting all the records that you should be getting. This is why the between operator is usually a bad choice for date columns. The between operator will return records on both ends. Think about this for integers.

Declare @Temp Table(Data int)

Insert Into @Temp Values(1)
Insert Into @Temp Values(2)
Insert Into @Temp Values(3)
Insert Into @Temp Values(4)
Insert Into @Temp Values(5)

Select *
From   @Temp
Where  Data Between 2 and 4

Output....

[tt][blue]
Data
-----------
2
3
4
[/blue][/tt]

This sometimes causes problems with dates because of the time component.

Let's do something similar for dates....

Declare @Temp Table(Data DateTime)

Insert Into @Temp Values('1/1/2007 3:15 PM')
Insert Into @Temp Values('1/31/2007 4:10 PM')
Insert Into @Temp Values('2/2/2007 9:30 AM')

Select *
From   @Temp
Where  Data Between '1/1/2007' and '1/31/2007'

[tt][blue]
Data
------------------------------------------------------
2007-01-01 15:15:00.000
[/blue][/tt]

In this case it didn't pick up the record that has '1/31/2007 4:10 PM' because the time caused it to be greater than the end date (of the between operator). In this case, if you want to pick up all the records from January 2007, you should do this...

Declare @Temp Table(Data DateTime)

Insert Into @Temp Values('1/1/2007 3:15 PM')
Insert Into @Temp Values('1/31/2007 4:10 PM')
Insert Into @Temp Values('2/2/2007 9:30 AM')

Select *
From   @Temp
Where  Data >= '1/1/2007' 
       and data < '2/1/2007'

[tt][blue]
Data
------------------------------------------------------
2007-01-01 15:15:00.000
2007-01-31 16:10:00.000
[/blue][/tt]

-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

or do what I like to do which is:

AND Opmgr.closeddate BETWEEN @BDate AND @EDate - '00:00:00.003'

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[monksnake]

Actually, monksnake... I think you meant sargable, not paresable. The between statement is sargable (and parsable).

Damn George, thanks for setting me straight, I mean WTF was I blabbing about.

<.