做多条件查询 得不到查询结果
[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 [email protected]+'%'+' or [email protected]+' ='')'+
'and (stuSex [email protected]+'%'+' or [email protected]+' ='')'+
'and (stuAge between [email protected]+' and [email protected]+' or stuAge between 1 and 100)'+
'and (stuAddress like '+'% [email protected]+'%'+'or [email protected]+' =''))'+'order by stuNumber'
----
exec fenye 0,'','','','',''
------解决方案--------------------
测试条件 @stuName @stuSex 不能用‘’来代替 [email protected]
@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 [email protected]+'%'''+' or [email protected]+' ='''')'+'and (stuSex [email protected]+'%'''+' or [email protected]+' ='''')'+'and (stuAge between [email protected]+' and [email protected]+' or stuAge between 1 and 100)'+'and (stuAddress like [email protected]+'%'''+'or [email protected]+' =''''))'+'order by stuNumber'PRINT @str--可以用print 打印出来语句 看看拼的对不对
------解决方案--------------------
将所有参数代入进去，[email protected] 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