While statement problem - 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

While statement problem

Author

Topic

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-16 : 11:53:29

Hi,

I am using SQl server 2005 and have issues executing the following procedure:

Create Procedure AmortScenarios (@Contract int)

Begin
Declare @Contract_Length int
Set @Contract_Length = (select COUNT(CAl_date) from #amort_36mo)
while Cal_date >='2010-02-01'
set @Contract_Length = @Contract_Length - 1

End
The error I get is :

Msg 156, Level 15, State 1, Procedure AmortScenarios, Line 3
Incorrect syntax near the keyword 'Begin'.

Can someone help me? In addition, I want to execute the procedure into a new table with only the procedure results there - how do i do that?

thanks much!

chadmat
The Chadinator

1974 Posts

Posted - 2010-11-16 : 12:03:10

You forgot AS

Create Procedure AmortScenarios (@Contract int)

AS <-- You need this

BEGIN

-Chad

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-16 : 12:11:26

Ok great thanks! it executed successfully..now how can I display the results from the procedure into a new temp table ?

thanks!

chadmat
The Chadinator

1974 Posts

Posted - 2010-11-16 : 12:26:53

I'm not sure what you are attempting to do. Is this the entire proc? Why are you taking @Contract as a param, but never using it? Maybe you should consider a Table-Valued function if you want to return the results as a table.

-Chad

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-16 : 12:36:18

I am not sure what a table-valued function is but you are right about @contract parameter..Basically I have a table with a list of the first date of each month for a period of 3 years. I want to create a procedure that will output the total number of months and then start decreasing this number by 1 month at a time. The table I am retrieving the dates is called #amort_36mo and here is the new procedure:

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

___

Now I try to call the procedire (to display its results) using call AmortScenarios ('2011-10-10')
but it gives me the following error: Incorrect syntax near '2011-10-10'.

Please let me know your thoughts Chad! Thanks!

chadmat
The Chadinator

1974 Posts

Posted - 2010-11-16 : 13:02:28

Still unclear to me. So you have a table with 1 DateTime column, and it has 36 rows? Each containing the first day of a month?

So it looks something like this:

1/1/2010
2/1/2010
3/1/2010
4/1/2010
...

for 3 years of months?

Then what do you want the Proc to return? The number of rows with a date > than the passed in paramenter? Or some sort of table that looks like this:

12
11
10
9
8
7
6
5
4
3
2
1

Sorry, I just don't understand the objective yet, so I can't give any advice.

-Chad

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-16 : 13:56:43

Ok no problem. I actually started with something more simple so that I get used to work with looping statements first..but I have this problem again:

So as you pointed out correctly we have the list with dates! next thing I want to do is to create a looping statement that will add the number 400 in a new columns across the first date and numbers 0 to the rest of the rows in the new column. I have the following statement and it gives me an error near keyword THEN

Declare @Initial_Fee int

If [cal_date] = (select MIN(cal_date) from #Amort_36mo)
then
set @Initial_Fee = 400 end;
else begin set @Initial_fee = 0 end;

what is the problem? Please help - thanks

chadmat
The Chadinator

1974 Posts

Posted - 2010-11-16 : 15:46:06

then should be begin
and you don't need the semicolons

-Chad

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-16 : 17:37:49

Thanks! going back to the looping statement: I have this list with dates (from above). I want to list across each date what is its count in the table. Example of the end result I want to have is below:

Date Count
2011-01-01 1
2011-02-01 2
2011-03-01 3
2011-04-01 4

I created this statement but when I execute, only number 1 populates down the column.. how can I restrict the loop statement on each row? thanks!

begin
Declare @Contract_Length int
Set @Contract_Length = (select count(CAl_date) from #Amort_36mo)
while @contract_length >= 2
set @Contract_Length = @Contract_Length - 1
End

Update #Amort_36mo
set Contract_Length = @Contract_Length

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources