Hi Allan,

You cannot specify sort order this way. See Parameterizing Result Order at http://www.sqlmag.com/Articles/Index.cfm?ArticleID=16495 for alternatives.

>Hi,
>
>I have a stored procedure in which a user passes a number of variables, one of which is a string seperated by commas representing the Order By part of the SQL. In the example below, the stored proc is being passed the string 'TransactionDetails.AmountMN, TransactionDetails.PanNoVC' in input variable @SortOrder.
>
>The SQL Select statement below doesn't work using this approach. Is there a way I can pass in a string containing the Order by clause and make this work?
>
>

>
>alter  PROCEDURE dbo.up_CustomReport1
>		@ReportControlID int,
>		@SwitchReportVC varchar(10),
>		@CUBranchID varchar(6),
>		@AmountHigh money,
>		@AmountLow money,
>		@SortOrder varchar(250),
>		@RecTypeCH char(1)
>			
>As
>
>Declare @AllAmountsBT bit
>
>if (@AmountLow = 0) and (@AmountHigh = 0)
>	Begin
>	  set @AllAmountsBT = 1
>	End
>Else
>	set @AllAmountsBT = 0
>
>
>SET NOCOUNT ON
>
>if (@AllAmountsBT = 1) and (@RecTypeCH = 'C')
>
>Begin
>
>SELECT TransactionDetails.TransactionDetailsID,
>TransactionDetails.ReportsID,
>TransactionDetails.ReportControlID,
>TransactionDetails.CUBranchID,
>CUBranch.BranchDescVC,
>TransactionDetails.CUSubBranchID,
>TransactionDetails.DeviceNoVC,
>TransactionDetails.TxnDateDT,
>TransactionDetails.PanNoVC,
>cast(TransactionDetails.PanNoVC as bigint) as PanNoBI,
>TransactionDetails.MemberAccountVC,
>TransactionDetails.AccountTypeCH,
>TransactionDetails.SubAccountNoCH,
>TransactionDetails.PrCodeCH,
>TransactionDetails.BranchVC,
>TransactionDetails.AmountMN,
>TransactionDetails.DRorCRch,
>TransactionDetails.PccCH,
>TransactionDetails.TraceNoVC,
>TransactionDetails.FeesMN,
>TransactionDetails.CardOwnerInstVC,
>TransactionDetails.RespCodeCH,
>TransactionDetails.SysTraceVC,
>ReportControl.ReportControlID,
>ReportControl.ReportEffectiveFromDT,
>ReportControl.ReportEffectiveToDT,
>Reports.ReportNameVC,
>Reports.ReportType,
>Reports.ATMorPOSCH,
>Reports.ReportIdentifierVC
>FROM TransactionDetails INNER JOIN
>Reports ON TransactionDetails.ReportsID = Reports.ReportsID INNER JOIN
>CUBranch ON TransactionDetails.CUBranchID = CUBranch.CUBranchID 
AND TransactionDetails.CUBranchID = CUBranch.CUBranchID INNER JOIN
>ReportControl ON TransactionDetails.ReportControlID = ReportControl.ReportControlID
>WHERE (ReportControl.ReportControlID = @ReportControlID) and
>	(TransactionDetails.CUSubBranchID = @CUBranchID) and
>	(Reports.ReportIdentifierVC  = @SwitchReportVC)
>ORDER BY @SortOrder
>
>return
>
>End
>
>