Sql server Order by according to sent params

Question

I'm sending four id's to the database to get the four questions. and their respected multiple choice options. Everything is fine but when user filtered the questions by 'id', Order by functionality is disturbing. please suggest your code how to get order by through sent parameter.

For Example:

If I'm sending @CaseId VARCHAR(MAX)= '34559|34558|34557|34556'
o/p coming as 34556
34557
34558
34559

If I'm using order by desc the condition fails if the user is not filtered through 'id'. Issue when the user is filtered by 'id'. I want o/p as the sent parameters order not by default order even user filtered or not.

Note: If User selected filter option id's are changing vice versa means asc to desc and desc to asc

Thanks in advance.

saida chary · Answer

STRING_SPLIT is pre-defined function SQL SERVER 2016 Onwords Other wise just use user defined function which was declared by Mr.Uday dbo .split Declare @CaseId VARCHAR(MAX)= '34559|34558|34557|34556' select * from ( SELECT value FROM STRING_SPLIT(@ CaseId  '|') )t order by t. value desc

saida chary · Answer

Mr. Uday thank you for ur suggestion I am expecting which should come in single select statement without temp table

Uday Suragani · Answer

Try as below. DECLARE @InTable TABLE (ID INT SortOrder INT NOT NULL IDENTITY(1 1) ); INSERT INTO @InTable SELECT slice FROM dbo.Split(@CaseId '|'); finally do order by SortOrder Note: In SSMS 2012 for compatibility level 130 dbo.split function will exists by default. For other version it may not exists please create a function dbo.split as follows.. CREATE FUNCTION [dbo].[Split] ( @Source NVARCHAR(MAX) @Delimiter CHAR(1) ) RETURNS @Slices TABLE (Slice nvarchar(max)) AS BEGIN DECLARE @Slice NVARCHAR(MAX) @Pos INT SET @Source = LTRIM(RTRIM(@Source))+ @Delimiter; SET @Pos = CHARINDEX(@Delimiter @Source 1) IF REPLACE(@Source @Delimiter '') '' BEGIN WHILE @Pos > 0 BEGIN SET @Slice = LTRIM(RTRIM(LEFT(@Source @Pos - 1))) IF @Slice '' BEGIN INSERT INTO @Slices VALUES(CAST(@Slice AS NVARCHAR(MAX))) END SET @Source = RIGHT(@Source LEN(@Source) - @Pos) SET @Pos = CHARINDEX(@Delimiter @Source 1) END END RETURN; END

Midhun T P · Answer

Hi Please provide the current query you are using.