Dynamic order by without using dynamic sql ?
- by Rohit
I have the following stored procedure which can be sorted ascending and descending on TemplateName,CreatedOn and UploadedBy. The following SP when runs doesnot sort records.if i replace 2,3,4 by columnname, i got an error message "Conversion failed when converting the nvarchar value 'Test Template' to data type int.".Please suggest how to achieve sorting.
CREATE PROCEDURE [dbo].[usp_SEL_GetRenderingTemplate]
(
@facilityID INT,
@sortOrder VARCHAR(5),
@sortExpression VARCHAR(100),
@errorCode INT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON ;
BEGIN TRY
SET @sortOrder = CASE @sortOrder
WHEN 'Ascending' THEN 'ASC'
WHEN 'Descending' THEN 'DESC'
ELSE 'ASC'
END
SELECT TemplateID,
TemplateName,
CreatedOn,
( [user].LastName + ' ' + [user].FirstName ) AS UploadedBy
FROM Templates
INNER JOIN [user] ON [user].UserID = Templates.CreatedBy
WHERE facilityid = @facilityID
ORDER BY CASE WHEN @sortExpression = 'TemplateName'
AND @sortOrder = 'ASC' THEN 2
WHEN @sortExpression = 'CreatedOn'
AND @sortOrder = 'ASC' THEN 3
WHEN @sortExpression = 'UploadedBy'
AND @sortOrder = 'ASC' THEN 4
END ASC,
CASE WHEN @sortExpression = 'TemplateName'
AND @sortOrder = 'DESC' THEN 2
WHEN @sortExpression = 'CreatedOn'
AND @sortOrder = 'DESC' THEN 3
WHEN @sortExpression = 'UploadedBy'
AND @sortOrder = 'DESC' THEN 4
END DESC
SET @errorCode = 0
END TRY
BEGIN CATCH
SET @errorCode = -1
DECLARE @errorMsg AS VARCHAR(MAX)
DECLARE @utcDate AS DATETIME
SET @errorMsg = CAST(ERROR_MESSAGE() AS VARCHAR(MAX))
SET @utcDate = CAST(GETUTCDATE() AS DATETIME)
EXEC usp_INS_LogException 'usp_SEL_GetFacilityWorkTypeList',
@errorMsg, @utcDate
END CATCH
END
