I need help with a Stored Procedure. 1

[elmorro]

Hi all,
I have written the following Stored Procedure:

DECLARE @ERRORCODE int
EXECUTE @ERRORCODE = ProcessTimeLog '12/1/2006', '12/10/2006'

I would like to modify this Stored Procedure so that the second date parameter always equals today's date. I made the following changes but I keep getting an error:

DECLARE @ERRORCODE int
EXECUTE @ERRORCODE = ProcessTimeLog '01/01/06', GetDate()

I get the following error when I execute the procedure:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ')'.

What am I doing wrong?

Thanks in advance,
elmorro

 

[AlexCuse]

@ERRORCODE sounds like a variable, not a stored procedure. What is the code for your proc, and what are you trying to accomplish?

You can only execute a valid SQL command, that is why you get an error.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Hmm.... that is odd. This little bit of code seems to work.

Declare @ErrorCode Int
Declare @EndDate DateTime
Set @EndDate = GetDate()

Execute @ErrorCode = ProcessTimeLog '1/1/2006', @EndDate
Select @ErrorCode

I don't know why your original code didn't work. Sorry.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Alex,

You can return a value from a stored procedure. Usually return values are there to indicate an error status, but it doesn't have to. So, I created this stored procedure to test the functionality.

Create Procedure ProcessTimeLog
	@Date1 DateTime,
	@Date2 DateTime
As
Return DateDiff(Day, @Date1, @Date2)

Then you can run this code to see the date diff.

Declare @ErrorCode Int
Declare @EndDate DateTime
Set @EndDate = GetDate()

Execute @ErrorCode = ProcessTimeLog '1/1/2006', @EndDate
Select @ErrorCode

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[elmorro]

Thank you both.

I was missing the following from my code:

Declare @EndDate DateTime
Set @EndDate = GetDate()


Now it works great!

Thank you once again,
elmorro

 

[AlexCuse]

I see. That almost reminds me of the way you would use a function from a class module to return a value, although I guess that would be value = execute.

Thanks for the lesson, and another thing to work on in my test db. I will now red flag my previous post :-(

ElMorro - what is in your actual proc ProcessTimeLog?

Ignorance of certain subjects is a great part of wisdom

 

[elmorro]

Yes AlexCuse, ProcessTimeLog is my procedure.

 

[AlexCuse]

Hehe you posted while I was playing with it. I was wondering if a problem was in there but it was already fixed.

Rough week, time to have a beer

Ignorance of certain subjects is a great part of wisdom