exec sp_executesql N'WITH SearchResult AS
( SELECT Row_Number() OVER (ORDER BY @SortExpression +'' '' + @SortDirection) AS RowID,
UserID,
FirstName,
LastName,
UserName,
Status,
UserRole
FROM dbo.Users
WHERE (Status = @Status OR @Status = -1)
AND (FirstName LIKE ''%''+@Keyword+''%'' OR LastName LIKE ''%''+@Keyword+''%'' OR UserName LIKE ''%''+@Keyword+''%'' OR @Keyword = '''')
) SELECT * FROM SearchResult WHERE RowID > @PageIndex * @PageSize AND RowID <= (@PageIndex + 1) * @PageSize',
N'@Keyword nvarchar(4000),@Status int,@PageIndex int,@PageSize int,@SortExpression nvarchar(8),@SortDirection nvarchar(4)',
@Keyword=N'',@Status=-1,@PageIndex=0,@PageSize=1,@SortExpression=N'USERNAME',@SortDirection=N'asc'
在上面的SQL语句中指定的排序参数 @SortExpression @SortDirection为什么不起作用,无论@SortDirection=N'asc'还是@SortDirection=N'desc',执行的结果始终是一样的顺序?
( SELECT Row_Number() OVER (ORDER BY @SortExpression +'' '' + @SortDirection) AS RowID,
UserID,
FirstName,
LastName,
UserName,
Status,
UserRole
FROM dbo.Users
WHERE (Status = @Status OR @Status = -1)
AND (FirstName LIKE ''%''+@Keyword+''%'' OR LastName LIKE ''%''+@Keyword+''%'' OR UserName LIKE ''%''+@Keyword+''%'' OR @Keyword = '''')
) SELECT * FROM SearchResult WHERE RowID > @PageIndex * @PageSize AND RowID <= (@PageIndex + 1) * @PageSize',
N'@Keyword nvarchar(4000),@Status int,@PageIndex int,@PageSize int,@SortExpression nvarchar(8),@SortDirection nvarchar(4)',
@Keyword=N'',@Status=-1,@PageIndex=0,@PageSize=1,@SortExpression=N'USERNAME',@SortDirection=N'asc'
在上面的SQL语句中指定的排序参数 @SortExpression @SortDirection为什么不起作用,无论@SortDirection=N'asc'还是@SortDirection=N'desc',执行的结果始终是一样的顺序?
解决方案 »
- 求大神帮忙修改下存储过程!谢谢了!
- 存储过程中用insert语句增加一条新记录,怎样立刻得到这条记录的ID号?
- 使用链接服务器,执行带返回参数的oracle存储过程,如何获取参数返回值?
- 两个表合并数据 求解
- Microsoft OLE DB Provider for SQL Server (0x80040E37)
- 高手 简单问题
- 原来数据库全部放在最后一个磁盘。现在要把磁盘那掉。如何移动数据库?
- 在SQL SERVER 里怎样建临时表?
- 请问如何设置两个数据库实时同步(SQL SERVER 7。0)
- 用不含"0"值相邻行,替换含"0"值行?
- 对sql 特殊字符处理,如ø750×75
- 急~!!!!!!!!!!!!!!!!!
insert into tb select 1,'c'
insert into tb select 2,'b'
insert into tb select 3,'a'declare
@ordername varchar(10),
@ordertype varchar(10),
@sql nvarchar(4000)
set @ordername='id'
set @ordertype='desc'
set @sql='select *,row_number() over(order by '+@ordername+' '+ @ordertype+')as a from tb'
exec (@sql)id name a
3 a 1
2 b 2
1 c 3
exec sp_executesql N'WITH SearchResult AS
( SELECT Row_Number() OVER (ORDER BY @SortExpression +'' '' + @SortDirection) AS RowID,
UserID,
FirstName,
LastName,
UserName,
Status,
UserRole
FROM dbo.Users
WHERE (Status = @Status OR @Status = -1)
AND (FirstName LIKE ''%''+@Keyword+''%''
OR LastName LIKE ''%''+@Keyword+''%''
OR UserName LIKE ''%''+@Keyword+''%''
OR @Keyword = '''') )
SELECT * FROM SearchResult
WHERE RowID > @PageIndex * @PageSize AND
RowID <= (@PageIndex + 1) * @PageSize' order by RowID asc, --**这里加上
N
'@Keyword nvarchar(4000),@Status int,@PageIndex int,@PageSize int,@SortExpression nvarchar(8),@SortDirection nvarchar(4)',
@Keyword=N'',@Status=-1,@PageIndex=0,@PageSize=1,@SortExpression=N'USERNAME',@SortDirection=N'asc'