动态存储过程出问题~解决马上给分

alter   proc   complexChangePage(@sort   varchar(20),@startIndex   int,@maxRows   int)
as
declare   @sql   varchar(5000);
if   len(@sort)=0
set   @sort= 'job_id '
set   @sql= 'select   job_id,job_desc,min_lvl,max_lvl,fname,lname,rank   from
(select   j.job_id,j.min_lvl,j.max_lvl,j.job_desc,ROW_NUMBER()   over(order   by   '+@sort+ ')  
as   rank,e.fname,e.lname   from   jobs   j   inner   join   employee   e   on   j.job_id=e.job_id)   as   job '  
exec   @sql


执行存储过程时

exec   complexChangePage   'job_id ',0,5
报错说那这段语句不是有效的标识符
还有个问题我可不以不传第一个参数也就是@sort,只传后面的参数
如果我只传两个参数它会默认为最后一个没有参数~

------解决方案--------------------
alter proc complexChangePage(@sort varchar(20)= ' ',@startIndex int=0,@maxRows int=0)
as
declare @sql varchar(5000);
if len(@sort)=0
set @sort= 'job_id '
set @sql= 'select job_id,job_desc,min_lvl,max_lvl,fname,lname,rank from
(select j.job_id,j.min_lvl,j.max_lvl,j.job_desc,ROW_NUMBER() over(order by '+@sort+ ')
as rank,e.fname,e.lname from jobs j inner join employee e on j.job_id=e.job_id) as job '
exec @sql

加上默认值就可以了
------解决方案--------------------
如果有该存储过程用在SQL2000里是会出错的
因为ROW_NUMBER() sql2000里没有