如何在SQL Server中传递用于order by子句的参数? [英] how to pass a parameter for order by clause in sql server?

问题描述

先生，

在我的前端应用程序中，我使用一个下拉列表和datagrid.下拉列表的目的是对数据进行排序.我将Ename，Esal，Dept的三个项目添加到了下拉列表中.

如果用户从下拉列表中选择一项，则该时间数据将显示所选项目的排序顺序.

我正在写存储过程，如:

hi sir,

in my front end application i take one dropdown list and datagrid. that dropdownlist purpose is sorting data. i take Ename, Esal, Dept three items to the dropdownlist.

if user select any one item from dropdownlist, that time data diplayed sorting order of selected item.

i am write stored procedure like:

create procedure sp_Sortdata
(
 @Orderbyclause varchar(50)
)
 as
   begin
        selecet * from emp order by @Orderbyclause 
    end

我正面临这个错误

and i am facing this error

The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

请更正我的查询先生，请提供解决方案

谢谢

please correct my query sir, please give a solution

Thanks

推荐答案

您可以使用 sp_executesql 解决问题，根据参数构建查询，然后使用sp_executesql执行.试试此链接为什么不使用sp_executesql? [ ^ ]

You can use sp_executesql for your issue, build the query based on parameter then exec with sp_executesql. Try this link Why not to use sp_executesql?[^]

@thatraja您好....为了提供更多帮助，我尝试为他编写过程....

@thatraja U r right....to help a bit more I tried to write the procedure for him....

CREATE PROC sp_Sortdata
@OrderByColumnName nvarchar(15)
AS
    DECLARE @SQLStatement nvarchar(max)
    SET @SQLStatement = N'select * from emp order by '+@OrderByColumn
    EXEC sp_executesql @statement = @SQLStatement

现在，使用
执行创建的存储过程

Now execute the created stored proc by using

exec sp_SortData @OrderByColumnName=''ColumnName''

这里的ColumnName是您要进行排序的列的名称

不是简单的:cool:

如果您喜欢我的答案，请对其投票或将其标记为答案.

Here ColumnName is the name of the column by which you want to do the sorting

Simple isn''t :cool:

If you like my answer please, vote it or mark it as answer.

DECLARE @STR VARCHAR(1000)

SET @STR = 'selecet * from emp order by '+  @Orderbyclause
EXECUTE(@STR)

这篇关于如何在SQL Server中传递用于order by子句的参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！