如何在order by子句中使用变量 [英] how to use variable in order by clause

问题描述

大家好，



我必须根据条件执行订单。为此，我写了下面的代码



对于这个我在尝试使用该变量后声明了一个变量。但是它没有用。

Hi All,

I have to perform order by based on condition. For this I have written the below code

For this First i have declared one variable after i am trying to use that variable.But it is not working .

DECLARE @order Nvarchar(50)
--set @order='DATEDIFF(day,EffectiveDate,getdate())';

set @order='Accountname DESC';

WITH Result as (
        SELECT ROW_NUMBER()
        OVER (ORDER BY @order) AS Row,
        Number,
        Accountname,
        LOB,
        History.Policystatus As [Status],
        Cast(Quote.EffectiveDate as date) As ExpirationDate ,
        DATEDIFF(day,EffectiveDate,getdate()) as NoOfDays
        FROM MyTable WITH (NOLOCK)
        WHERE Userid='1'  )
 select * from Result where Row Between 1 AND 10

请帮忙。



谢谢，

Nagasubba Reddy

Please help on this.

Thanks,
Nagasubba Reddy

推荐答案

嗨Nagasubba，



您可以使用动态SQL。

示例： -



例如我只拍了3,4列..

Hi Nagasubba,

You can use dynamic SQL .
Example :-

For Example i have taken only 3,4 column..

Create Table #MyTable (Number varchar(max),Accountname varchar(max) ,LOB varchar(max) )
INSert into #MyTable
Values ( 'one','vijay dinanath','Developemnt'), 
 ( 'Two','Mangal','Developemnt'), 
 ( 'Three','Amrendra','Developemnt')

DECLARE @order Nvarchar(50)
--set @order='DATEDIFF(day,EffectiveDate,getdate())';

set @order='Accountname asc';


 Set @Query='
;WITH Result as (
        SELECT ROW_NUMBER()
        OVER (ORDER BY '+@order+') AS Row,
        Number,
        Accountname,
        LOB
        FROM #MyTable WITH (NOLOCK)
         )
 select * from Result where Row Between 1 AND 10'
 Exec (@Query);

您需要使用动态SQL来指定 ORDER BY 子句。





You need to use dynamic SQL to to be able to specify the ORDER BY clause dynamically.

DECLARE @order Nvarchar(50) = 'Accountname DESC';

DECLARE @newLine AS CHAR(2) = CHAR(13) + CHAR(10)

DECLARE @sql NVARCHAR(MAX) = N'SELECT' + @newLine +
        '    Number,' + @newLine +
        '    Accountname,' + @newLine +
        '    LOB,' + @newLine +
        '    History.Policystatus As [Status],' + @newLine +
        '    Cast(Quote.EffectiveDate as date) As ExpirationDate ,' + @newLine +
        '    DATEDIFF(day,EffectiveDate,getdate()) as NoOfDays' + @newLine +
        'FROM MyTable WITH (NOLOCK)' + @newLine +
        'WHERE Userid=''1''' + @newLine +
        'ORDER BY ' + @order;

--PRINT @sql;
EXEC sp_execute @sql;

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