如何在存储过程中作为参数添加订单？ [英] How to Add order by as a parameter in stored procedure?

问题描述

嘿伙计，



i有一个存储过程可以从数据库中选择，


i希望按列添加订单作为参数



我想添加排序类型作为参数



EX：从学生订单中选择* @Column_Name @Sort_Type



@Column_Name是可排序列

@Sort_Type是ASC或DESC



任何想法

解决方案

尝试这个：

 创建 程序 MyProc 
  @ Column_Name   VARCHAR （ 30  ），
  @ Sort_Type   VARCHAR （ 10 ）
  AS  
  BEGIN  
  DECLARE   @sql   VARCHAR （ 2000 ）
 
  SET   @sql  = '  SELECT *' + 
 '  FROM Students' + 
 ' < span class =code-string> ORDER BY' +  @ Column_Name  + ' < span class =code-string>' +  @ Sort_Type  
  EXEC  （ @ sql ）
 
  END  

govardhan4u [ ^ ]写道，请参阅本文：在存储过程中构建动态SQL [< a href =http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Proceduretarget =_ blanktitle =New Window> ^ ]

Hey Guys,

i have a stored procedure which selects from the data base,

i want to add order by column as a parameter

and i want to add the sort type as a parameter too

EX: select * from Students order by @Column_Name @Sort_Type

@Column_Name is the sortable column
@Sort_Type is ASC or DESC

Any Ideas

解决方案

Try this:

CREATE PROCEDURE MyProc
     @Column_Name VARCHAR(30),
     @Sort_Type VARCHAR(10)
AS
BEGIN
    DECLARE @sql VARCHAR(2000)

    SET @sql = 'SELECT * ' +
               'FROM Students ' + 
               'ORDER BY ' + @Column_Name + ' ' + @Sort_Type
    EXEC (@sql)

END

As govardhan4u[^] wrote, see this article: Building Dynamic SQL In a Stored Procedure[^]

这篇关于如何在存储过程中作为参数添加订单？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！