分页存储过程拼接异常

分页存储过程拼接错误
做多条件查询 得不到查询结果
[code=SQL][/code]
alter proc fenye
@page int,@stuName nvarchar(50),
@stuSex nvarchar(50),
@stuAge1 nvarchar(50),@stuAge2 nvarchar(50),
@stuAddress nvarchar(50)
as
declare @str nvarchar(200) 
set @str='select top 10 * from student '
+'where ((stuNumber not in ( select top '+cast(@page*10 as varchar(30))+' '
+' stuNumber from student order by stuNumber))'+
  'and (stuName like '+'%'+@stuName+'%'+' or '+@stuName+' ='')'+
'and (stuSex like'+'%'+@stuSex+'%'+' or '+@stuSex+' ='')'+
'and (stuAge between '+@stuAge1+' and '+@stuAge2+' or stuAge between 1 and 100)'+
'and (stuAddress like '+'% '+@stuAddress+'%'+'or '+@stuAddress+' =''))'+'order by stuNumber'
----
exec fenye 0,'','','','',''

[解决办法]
测试条件 @stuName @stuSex 不能用‘’来代替 需要制定一个世纪名字@stuAddress
@stuAge1 @stuAge2 定义成int 类型的
拼接的地方好多地方引号不对

SQL code

declare @str nvarchar(8000)--你这个长度也太小了，我给改大了  set @str='select top 10 * from student '+'where ((stuNumber not in ( select top '+cast(@page*10 as varchar(30))+' '+' stuNumber from student order by stuNumber))'+  'and (stuName like '+'''%'+@stuName+'%'''+' or '+@stuName+' ='''')'+'and (stuSex like'+'''%'+@stuSex+'%'''+' or '+@stuSex+' ='''')'+'and (stuAge between '+@stuAge1+' and '+@stuAge2+' or stuAge between 1 and 100)'+'and (stuAddress like '+'''%'+@stuAddress+'%'''+'or '+@stuAddress+' =''''))'+'order by stuNumber'PRINT @str--可以用print  打印出来语句 看看拼的对不对
[解决办法]
将所有参数代入进去，把@str这个字符串 print 出来执行看有什么问题。
[解决办法]
SQL code
ALTER PROC fenye          @page       INT,          @stuName    NVARCHAR(50),          @stuSex     NVARCHAR(50),          @stuAge1    NVARCHAR(50),          @stuAge2    NVARCHAR(50),          @stuAddress NVARCHAR(50)AS  DECLARE    @str NVARCHAR(8000)  SET @str = 'select top 10 * from student ' +             'where ((stuNumber not in ( select top ' +              Cast(@page * 10 AS VARCHAR(30)) +              ' ' + ' stuNumber from student order by stuNumber))' +              'and (stuName like ' + '''%' + @stuName + '%''' +              ' or ' + @stuName + ' ='''')'+              'and (stuSex like' + '''%'+               @stuSex + '%''' + ' or ' +                @stuSex + ' ='''')' +                'and (stuAge between ' +                 @stuAge1 + ' and ' +                 @stuAge2 + ' or stuAge between 1 and 100)' +                 'and (stuAddress like ' + '''%' + @stuAddress +                 '%''' + 'or ' + @stuAddress + ' =''''))' +                  'order by stuNumber'  PRINT @str