Sql server Order by according to sent params

saida chary

9y966

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.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

saida chary9y ago
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
+1
Midhun Tp9y ago
Hi,

Please provide the current query you are using.
-1
saida chary9y ago
Mr. Uday thank you for ur suggestion

I am expecting which should come in single select statement without temp table
+1
Uday Suragani9y ago
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

-1