SQL 在存储过程中传递动态顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15085149/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Passing dynamic order by in stored procedure
提问by Jitendra Pancholi
I am creating below stored procedure.
我正在创建下面的存储过程。
declare @PageNum as Int
declare @PerPageResult as Int
declare @StartDate as varchar(25)
declare @EndDate as varchar(25)
declare @SortType as Varchar(50)
declare @SortDirection as Varchar(4)
set @PageNum=1
set @PerPageResult=20
set @StartDate='2008-02-08'
set @EndDate='2015-02-08'
set @SortType='RegDate'
set @SortDirection='Desc'
declare @Temp Table(RowNum int, RegDate Date, Registered int, Female int, Male int, [Join] int, Rebill int, TotalPointsEarned int, Expire int)
declare @sort varchar(50)
Insert into @Temp
Select ROW_NUMBER() over (order by @SortType+' '+@SortDirection) As RowNum, * From (
SELECT
CAST(m.registrationdate AS Date) as RegDate,
count(m.id) Registered,
count(CASE WHEN m.gender='F' then 'F' end) As Female,
count(CASE WHEN m.gender='M' then 'M' end) As Male
count(CASE WHEN p.paymenttransactiontype='N' then 'N' end) As [Join],
count(CASE WHEN p.paymenttransactiontype='R' then 'R' end) As Rebill,
count(m.tokensearned) As TotalPointsEarned,
count(CASE WHEN p.paymenttransactiontype='E' then 'E' end) As Expire
from member m
join payment p on m.id=p.id_member
join user_role u on u.member_id=m.id
where u.role_id <> 3
and CAST(m.registrationdate AS Date) > @StartDate and CAST(m.registrationdate AS Date) < @EndDate
GROUP BY CAST(m.registrationdate AS Date)
) as aa
Select * from @Temp Where RowNum>((@PageNum-1)*@PerPageResult) and RowNum<=@PerPageResult * @PageNum
Order by @SortType+' '+@SortDirection
In above when i pass the Order byclause dynamically, its not sorting the data properly but when i write column name explicitly, it works fine. Might be its taking @SortType+' '+@SortDirectionas varcharrather than Date
在上面，当我Order by动态传递子句时，它没有正确对数据进行排序，但是当我明确写入列名时，它工作正常。可能是它把它@SortType+' '+@SortDirection当作varchar而不是Date
I tried writing Order by case when (@Sort='RegDate' and @SortDirection='Desc') Then RegDate End Desc, but it didn't work
我尝试写作Order by case when (@Sort='RegDate' and @SortDirection='Desc') Then RegDate End Desc，但没有奏效
How can i pass order by dynamically here.
我如何在这里动态传递订单。
Edit: @Andomar: I tried your provided solution and added one more field for Date type. And it didn't work too.
编辑：@Andomar：我尝试了您提供的解决方案，并为日期类型添加了一个字段。它也不起作用。
below is what i did.
下面是我所做的。
create table t1 (id int, name varchar(50), dt date);
insert t1 values
(1, 'Chihiro Ogino','2009-02-08'),
(2, 'Spirit of the Kohaku River','2008-02-08'),
(3, 'Yubaba','2012-02-08');
declare @sortColumn varchar(50) = 'dt'
declare @sortOrder varchar(50) = 'ASC'
select *
from t1
order by
case
when @sortOrder <> 'ASC' then 0
when @sortColumn = 'id' then id
end ASC
, case
when @sortOrder <> 'ASC' then ''
when @sortColumn = 'name' then name
end ASC
, case
when @sortOrder <> 'ASC' then ''
when @sortColumn = 'dt' then name
end ASC
, case
when @sortOrder <> 'DESC' then 0
when @sortColumn = 'id' then id
end DESC
, case
when @sortOrder <> 'DESC' then ''
when @sortColumn = 'name' then name
end DESC
, case
when @sortOrder <> 'DESC' then ''
when @sortColumn = 'dt' then name
end DESC
回答by Andomar
You can use a complicated order byclause. That requires one casefor each sort direction and each data type. With this example dataset:
您可以使用复杂的order by子句。这需要case每个排序方向和每个数据类型一个。使用此示例数据集：
create table t1 (id int, name varchar(50), created date);
insert t1 values
(1, 'Chihiro Ogino', '2012-01-01'),
(2, 'Spirit of the Kohaku River', '2012-01-03'),
(3, 'Yubaba', '2012-01-02');
You could use an order byclause like:
您可以使用以下order by子句：
declare @sortColumn varchar(50) = 'created'
declare @sortOrder varchar(50) = 'DESC'
select *
from t1
order by
case
when @sortOrder <> 'ASC' then 0
when @sortColumn = 'id' then id
end ASC
, case
when @sortOrder <> 'ASC' then ''
when @sortColumn = 'name' then name
end ASC
, case
when @sortOrder <> 'ASC' then cast(null as date)
when @sortColumn = 'created' then created
end ASC
, case
when @sortOrder <> 'DESC' then 0
when @sortColumn = 'id' then id
end DESC
, case
when @sortOrder <> 'DESC' then ''
when @sortColumn = 'name' then name
end DESC
, case
when @sortOrder <> 'DESC' then cast(null as date)
when @sortColumn = 'created' then created
end DESC
Working example at SQL Fiddle.
Another option is to create the query dynamically, and run it with exec. For example:
另一种选择是动态创建查询，并使用exec. 例如：
declare @sql nvarchar(max)
set @sql = 'select * from YourTable order by ' + @sortColumn + ' ' + @sortDir
exec (@sql)
回答by rlarian
@Andomar's answer help solve a similar issue. I needed to sort on any number of 23 different columns, in any order. I ended up with the following:
@Andomar 的回答有助于解决类似的问题。我需要以任意顺序对任意数量的 23 个不同列进行排序。我最终得到了以下结果：
create table sorting(ID int, columnName varchar(50), sort varchar(10), position int)
insert into sorting
values(1,'column1','DESC',1),
(1,'column2','ASC',2),
...
(1,'columnN','DESC',N)
Adding parameter @sort to the SP to identify the entries in sorting:
将参数@sort 添加到 SP 以识别排序中的条目：
ORDER BY ISNULL(STUFF((SELECT ', ' + a.columnName + ' ' + a.sort
FROM sorting a
WHERE a.ID = @sort
ORDER BY a.position ASC
FOR XML PATH('')), 1, 2, ''),NULL)
回答by Vamshi CH
There are two basic approaches to building dynamically orderable stored procedures:
构建动态可排序的存储过程有两种基本方法：
Pass in the
ORDER BYclause as a parameter to the stored procedure. In the stored procedure, build up the SQL statement in a string and then execute this statement usingEXECorsp_ExecuteSql.-- This Method is used when your Column names are dynamic -- We need to create a dynamic query and Execute it as shown below. CREATE PROCEDURE getEmployees ( @OrderByClause varchar(100) ) AS -- Create a variable @SQLStatement DECLARE @SQLStatement varchar(255) -- Enter the dynamic SQL statement into the -- variable @SQLStatement SELECT @SQLStatement = 'SELECT EmployeeID, FirstName, LastName, SSN, Salary FROM Employees ORDER BY '+ @OrderByClause+'' -- Execute the SQL statement EXEC(@SQLStatement)Pass in the column to sort by and then use a
CASEstatement in theORDER BYclause to order the results according to the input parameter value.--This method is used when you column name is not dynamic SELECT EmployeeID, FirstName, LastName, SSN, Salary FROM Employees ORDER BY CASE WHEN @ColumnName='LastName' THEN LastName WHEN @ColumnName='Salary' THEN CONVERT(varchar(50), Salary) WHEN @ColumnName='SSN' THEN SSN END
将
ORDER BY子句作为参数传递给存储过程。在存储过程中，以字符串形式构建 SQL 语句，然后使用EXEC或执行该语句sp_ExecuteSql。-- This Method is used when your Column names are dynamic -- We need to create a dynamic query and Execute it as shown below. CREATE PROCEDURE getEmployees ( @OrderByClause varchar(100) ) AS -- Create a variable @SQLStatement DECLARE @SQLStatement varchar(255) -- Enter the dynamic SQL statement into the -- variable @SQLStatement SELECT @SQLStatement = 'SELECT EmployeeID, FirstName, LastName, SSN, Salary FROM Employees ORDER BY '+ @OrderByClause+'' -- Execute the SQL statement EXEC(@SQLStatement)传入要排序的列，然后
CASE在ORDER BY子句中使用语句根据输入的参数值对结果进行排序。--This method is used when you column name is not dynamic SELECT EmployeeID, FirstName, LastName, SSN, Salary FROM Employees ORDER BY CASE WHEN @ColumnName='LastName' THEN LastName WHEN @ColumnName='Salary' THEN CONVERT(varchar(50), Salary) WHEN @ColumnName='SSN' THEN SSN END
回答by Maryam Arshi
something like this should work :
这样的事情应该工作：
ORDER BY
CASE WHEN @SortDirection = 'ASC' THEN @SortType END ASC,
CASE WHEN @SortDirection = 'DESC' THEN @SortType END DESC
