如何在SQL Server函数中使用动态顺序 [英] How to use dynamic order by in SQL server function

问题描述

我必须根据需求从前端传递参数然后根据该参数对函数进行排序..



那么我该如何提供排序功能功能

请帮帮我..

I have to pass parameter from front end based on requirement then based on that parameter the function would be sorted..

So how can I provide sorting feature in function
Please help me..

ALTER FUNCTION [dbo].[Func_GetCamp]
(
 @YearID int,
 @FederationID int,
 
 @SortOrder VARCHAR(MAX)
)
RETURNS 
@GetStateDistributionSummary TABLE 
(
 campaignID int,
 campaignName varchar(100),
 campaignControlNumber int,
 DistributionID int,
 AllocationDate datetime,
 DistributionNumber int,
 DistributionAmount decimal(18,2)
)
AS
BEGIN
 
 IF(@CampaignTypeID=0)
	BEGIN
		SET @CampaignTypeID=null
	END
	
	
SET @SortOrder = @SortOrder
 INSERT INTO @GetStateDistributionSummary
 
	SELECT
		campaign.campaignID,
		campaignName =
		CASE 
			WHEN LEN(campaign.name) > 25 THEN SUBSTRING ( campaign.name , 1 , 25 ) + '...'
			ELSE campaign.name
		END,
		CAST(campaign.controlNumber AS int) AS campaignControlNumber,
		allocation.DistributionID,
		allocation.allocationdate,
		Distribution.DistributionNumber,
		SUM(allocationCampaign.allocatedAmount) AS distributionAmount
	from 
		pledgenet.Allocation Allocation,
		pledgenet.allocationCampaign allocationCampaign,
		pledgenet.Distribution Distribution,
		pledgenet.campaign campaign
	WHERE 
		Allocation.YearID = @YearID
	AND 
		Allocation.FederationID = @FederationID
	)
	
	AND
        campaign.stateID =@StateID	
	GROUP BY 
		campaign.campaignID,
		CASE 
			WHEN LEN(campaign.name) > 25 THEN SUBSTRING ( campaign.name , 1 , 25 ) + '...'
			ELSE campaign.name
		END,
		CAST(campaign.controlNumber AS int),
		allocation.DistributionID,
		allocation.allocationdate,
		Distribution.DistributionNumber	
 RETURN 
END

SHOUTING删除 - OriginalGriff [/ edit]

[edit]SHOUTING removed - OriginalGriff[/edit]

推荐答案

看看这里：

在存储中构建动态SQL程序 [ ^ ]

动态查询LINQ到实体和表达式 [ ^ ]

使用Linq进行动态查询 [ ^ ]

使用动态查询 [ ^ ]

动态查询示例 [ ^ ]

在SQL Server中编写安全动态SQL [ ^ ]

Have a look here:
Building Dynamic SQL In a Stored Procedure[^]
Dynamic Querying with LINQ-to-Entities and Expressions[^]
Dynamic query with Linq[^]
Using Dynamic Queries[^]
Dynamic Query Sample[^]
Writing Secure Dynamic SQL in SQL Server[^]

这篇关于如何在SQL Server函数中使用动态顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！