order by后面带参数的方法

USE [QPGameGirlDB]
GO
/****** 对象:  StoredProcedure [dbo].[UP_Rank]    脚本日期: 05/20/2011 16:28:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[UP_Rank]
@topvalue varchar(1000),
@orderFields varchar(5000)
 AS   
  Declare @sql varchar(5000); -- 条件语句(不加 where，可带 group by 分组条件，分组条件需要带 group by 关键字)
	if(@topvalue='')
set @sql ='SELECT
	A.UserID, A.Constellation, A.Hot, A.Charm, A.Time, A.FavouriteGame, A.Star, A.Star_Num, A.Remark,A.Birthday, A.Hobby, A.BloodType, A.Height, A.City
	,B.Accounts as NickName,ROW_NUMBER() over(order by '+@orderFields+ ' desc) as RowNum,(Select count(*) From Fans Where UserID=A.UserID) As FansCount
    FROM [UserData] as A
LEFT JOIN dbo.GameAccountsInfo B on B.UserID=A.UserID
  order by '+@orderFields+ ' desc';
   else
set @sql ='SELECT top('+@topvalue+')
	A.UserID, A.Constellation, A.Hot, A.Charm, A.Time, A.FavouriteGame, A.Star, A.Star_Num, A.Remark,A.Birthday, A.Hobby, A.BloodType, A.Height, A.City
	,B.Accounts as NickName,ROW_NUMBER() over(order by '+@orderFields+ ' desc) as RowNum,(Select count(*) From Fans Where UserID=A.UserID) As FansCount
    FROM [UserData] as A
LEFT JOIN dbo.GameAccountsInfo B on B.UserID=A.UserID
  order by '+@orderFields+ ' desc';

exec(@sql)

转载于:https://www.cnblogs.com/_cloudy/archive/2011/05/20/2052149.html