Problem with IF

It is a procedure that does the paging on the friends table, the variable @.PageWay is the ordering that the return table have to appear, ASC or DESC.

Why it's accusing error on if clause? "if(@.PageWay= 1)"

createProcedure teste2

@.user_idint,

@.PageIndexint,

@.PageSizeint,

@.PageOrdervarchar(10),

@.PageWaybit

As

Begin

Declare @.FirstRowint,

@.LastRowint,

@.Recordsint,

@.Paginasfloat,

@.Pagesint

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

@.LastRow= @.PageSize+(@.PageIndex- 1)* @.PageSize;

With invitationas

(

Select*,

Row_Number()over(orderby friend_idasc)as RowNumber

from friendswhere [user_id]=(@.user_id)and invited=(1)

)

if(@.PageWay= 1)

Begin

Select*from invitationwhere RowNumberbetween @.FirstRowand @.LastRoworderby

casewhen @.PageOrder='creation'then creationendasc,

casewhen @.PageOrder='e_mail'then e_mailendasc

End

else

Begin

Select*from invitationwhere RowNumberbetween @.FirstRowand @.LastRoworderby

casewhen @.PageOrder='creation'then creationenddesc,

casewhen @.PageOrder='e_mail'then e_mailenddesc

End

Set @.Records=(SelectCount(*)as'amigos'From friendswhere [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.

I think as you must put a semicolon before you start WITH statement, you must put something after.|||

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.