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

Procedure error

Author

Topic

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-30 : 19:03:53

Hi I have the following procedure:

Create Procedure AmortScenarios (@Contract DATETIME)
as
Begin
Declare @Contract_Length int
Set @Contract_Length = (select COUNT(CAl_date) from #amort_36mo where (Cal_date) = @Contract)
while @contract_length >= 1
set @Contract_Length = @Contract_Length - 1
End

Declare @Monthly_Amorti int;
Set @monthly_amorti =
(select (SUM(quarterly_main_fee) + SUM(fees))/@Contract_Length
from #amort_36mo)
_____________

The procedure executes successfully but when I call the procedure using this command, it gives me an error:

call AmortScenarios ('2015-12-28')

Error:

Incorrect syntax near '2015-12-28'.

Can anyone help me why this is happening? thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-30 : 19:04:33

EXEC AmortScenarios '2015-12-28'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-30 : 19:10:05

thanks Tara! it worked - however I am getting another error now - Divide by zero error encountered. where could the problem be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-30 : 19:20:47

It's where you are doing division. You can't divide by zero. It's against the rules of math. So what do you want to do in that situation?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-30 : 19:25:45

well the value @contract_length is not supposed to be zero...do you know why my constraints are not executing properly? thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-30 : 19:29:46

I'm confused by the BEGIN/END in your stored procedure code. Is the @Monthly_Amorti part supposed to be inside the stored procedure? Could you post your actual code rather than just a snippet?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-30 : 19:34:40

Ok so I replaced the @contract_length with the number and now I am not getting any errors. However, I cannot see the results from the executed procedure? How do I display the results?

This is the most recent code:

Create Procedure AmortScenarios (@Contract DATETIME)
as

Declare @Contract_Length int
Set @Contract_Length = (select COUNT(CAl_date) from #amort_36mo where (Cal_date) = @Contract)
----while @contract_length > 1
----set @Contract_Length = @Contract_Length - 1

Declare @Monthly_Amorti int;
Set @monthly_amorti =
(select (SUM(quarterly_main_fee) + SUM(fees))/10-----@Contract_Length
from #amort_36mo)

exec AmortScenarios '2014-12-28'
drop procedure AmortScenarios

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-30 : 19:38:10

Well what do you want to display? So far in your code, you are just storing values into variables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-30 : 19:43:01

You haven't provided enough information for us to be able to help. You should start with a description of what you are trying to do, provide sample data, and expected output. We can't just read your code and know what you want it to do.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-01 : 02:15:23

You need to SELECT the variable

Create Procedure AmortScenarios (@Contract DATETIME)
as

Declare @Contract_Length int
Set @Contract_Length = (select COUNT(CAl_date) from #amort_36mo where (Cal_date) = @Contract)
----while @contract_length > 1
----set @Contract_Length = @Contract_Length - 1

Declare @Monthly_Amorti int;
Set @monthly_amorti =
(select (SUM(quarterly_main_fee) + SUM(fees))/10-----@Contract_Length
from #amort_36mo)

SELECT @monthly_amorti

GO

exec AmortScenarios '20141228'

Also see why you need to use unambigious date format
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-01 : 10:43:17

He deleted his post where he posted his entire stored procedure. It was in between my last two replies.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-01 : 13:46:28

yes this is absolutely correct - i needed to select the results into the table. everything works now - thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources