Can you see what is wrong here? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Can you see what is wrong here?

Author

Topic

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-03-21 : 15:46:51

-- It's accusing error on the if clause on line 30

create Procedure test

@user_id int,
@PageIndex int,
@PageSize int,
@PageOrder varchar(10),
@PageWay bit

As
Begin

Declare @FirstRow int,
@LastRow int,
@Records int,
@Paginas float,
@Pages int

Select @FirstRow = ( @PageIndex - 1) * @PageSize + 1,
@LastRow = @PageSize + (@PageIndex - 1) * @PageSize;

With invitation as
(
Select *,
Row_Number() over (order by friend_id asc) as RowNumber
from friends where [user_id]=(@user_id) and invited=(1)
)

if (@PageWay = 1)
Begin
Select * from invitation where RowNumber between @FirstRow and @LastRow order by creation asc
End
else
Begin
Select * from invitation where RowNumber between @FirstRow and @LastRow order by creation desc
End

Set @Records = (Select Count(*) as 'amigos' From friends where [user_id]=(@user_id) and invited=(1))

Set @Paginas = (Convert(Float,@Records) / Convert(Float,@PageSize))

Set @Pages = Ceiling(@Paginas)

return @Pages

End
Go

-- Thank you very much

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-21 : 15:49:43

What is the error?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-03-21 : 16:13:58

Thanks for reply tkizer. Here is the error.

Msg 156, Level 15, State 1, Procedure teste2, Line 28
Incorrect syntax near the keyword 'if'.

That is the only one.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 16:36:44

Can you do a cte WITH without column names?

Peter Larsson
Helsingborg, Sweden

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-03-21 : 17:02:05

unfortunately not, why?
That is very strange because when I leave with just one of conditions, it works fine.

Select * from invitation where RowNumber between @FirstRow and @LastRow order by
case when @PageOrder = 'creation' then creation end asc,
case when @PageOrder = 'e_mail' then e_mail end asc

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-03-21 : 19:39:43

I think is something wrong like you must put a semi colon before you start CTE.

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-03-21 : 20:58:56

I discovered the error. After you define CTE you must use it in the next statement, otherwise you will got a message error. So I just put the following query before the If statement "Select Count(*) from invitation", and worked just fine.
There is the message when you put a Select query that not use the CTE:

Msg 422, Level 16, State 4, Procedure teste2, Line 30
Common table expression defined but not used.

Thank you very much, for had seemed my post.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources