I’d like to create a procedure which later will have 5 integer parameters as an input. Within these procedure I want to sort those integer values in ascending order and later update a table. I did it with case whens so that I don’t lose the information of each parameter (Background: Additional to this, each parameter is connected to a string which I need so sort in the same order. But sorting those integer first is faster.)
Somehow I can’t get the sorting for the middle part. Min and Max Value works fine:
DECLARE @tableX TABLE(c1 INT, c2 INT, c3 INT, c4 INT, c5 INT);
INSERT @tableX(c1,c2,c3,c4,c5)
SELECT 2,1,3,0,1
UNION ALL SELECT 3,4,5,2,2
UNION ALL SELECT 5,4,3,1,1
UNION ALL SELECT 3,1,2,0,10;
SELECT
--int1
c1 = CASE
WHEN c1 >= c2 AND c1 >= c3 AND c1 >= c4 AND c1 >= c5 THEN c1
WHEN c2 >= c1 AND c2 >= c3 AND c2 >= c4 AND c2 >= c5 THEN c2
WHEN c3 >= c1 AND c3 >= c2 AND c3 >= c4 AND c3 >= c5 THEN c3
WHEN c4 >= c1 AND c4 >= c2 AND c4 >= c3 AND c4 >= c5 THEN c4
ELSE c5 END,
--int2
--c2 = CASE
-- WHEN c1 >= c2 AND c1 >= c3 AND c1 >= c4 AND c1 >= c5 THEN
-- CASE WHEN c2 >= c3 AND c2 >= c4 AND c2 >= c5 THEN c2 ELSE
-- CASE WHEN c3 >= c2 AND c3 >= c5 THEN
-- CASE WHEN c4 >= c5 THEN
-- WHEN c2 >= c3 AND c2 >= c4 AND c2 >= c5 THEN
-- CASE WHEN c2 >= c3 AND c2 >= c4 AND c2 >= c5 THEN c2 END
--ELSE
--CASE WHEN c3 >= c2 AND c3 >= c4 AND c3 >= c5 THEN c3 ELSE
--CASE WHEN c4 >= c5 THEN c4 ELSE c5 END END END END,
--int3
c3 = NULL,
--in4
c4 = NULL,
--in5
c5 = CASE
WHEN c1 <= c2 AND c1 <= c3 AND c1 <= c4 AND c1 <= c5 THEN c1
WHEN c2 <= c1 AND c2 <= c3 AND c2 <= c4 AND c2 <= c5 THEN c2
WHEN c3 <= c1 AND c3 <= c2 AND c3 <= c4 AND c3 <= c5 THEN c3
WHEN c4 <= c1 AND c4 <= c2 AND c4 <= c3 AND c4 <= c5 THEN c4
ELSE c5 END
FROM @tableX;
Can someone give a hint for the middle part?
Advertisement
Answer
I don’t know exactly how you want to use the sorted integers, but why not let SQL do the sorting for you? the below, creates a temp table (@s), with the five integers (passed in as parameters?) in it and sorts them.
declare @ip1 int = 2 declare @ip2 int = 1 declare @ip3 int = 3 declare @ip4 int = 0 declare @ip5 int = 1 declare @s table (v int) INSERT @s(v) values((@ip1)), ((@ip2)), ((@ip3)), ((@ip4)), ((@ip5)) insert select v from @s order by v
What you do in the stored proc with this sorted list is another question…
Ok, here’s a script to try and work through. It is T-SQL. Please run it and look at the output. the first part creates a dummy table To represent your table and populates it with some data. It assumes that your table has an integer Primary Key.
CREATE TABLE dbo.sortTbl
( id int IDENTITY(1,1) Primary Key NOT NULL,
i1 int NOT NULL, i2 int NOT NULL, i3 int NOT NULL,
i4 int NOT NULL, i5 int NOT NULL,
s1 varchar(20) NULL, s2 varchar(20) NULL, s3 varchar(20) NULL,
s4 varchar(20) NULL, s5 varchar(20) NULL)
insert sortTbl(i1, i2, i3, i4, i5, s1, s2, s3, s4, s5)
Values ((3), (1), (0), (5), (7), ('fog'), ('snap'), ('dead'), ('yellow'), ('lox')),
((6), (2), (12), (1), (8),('tree'), ('saw'), ('earn'), ('ran'), ('que')),
((5), (6), (0), (2), (3),('like'), ('car'), ('hood'), ('wash'), ('man'))
The next part is the script that generates the extra ten columns from the data in this sample table (`sortTbl’). It works even if the original values are not only 1 through 5, but within each row there should not be duplicates.
declare @T table (id int primary key not null,
ni1 int null, ni2 int null, ni3 int null,
ni4 int null, ni5 int null,
ns1 varchar(20) null, ns2 varchar(20) null,
ns3 varchar(20) null, ns4 varchar(20) null,
ns5 varchar(20) null)
insert @t(id) select id from sortTbl
declare @d table (id int, val int, strVal varchar(20))
declare @id int = 0
declare @S table (rn int primary key identity not null,
id int, colOrder int null, val int, strVal varchar(20))
While exists (Select * from @t
Where id > @id) Begin
select @id = Min(id)
from sortTbl where id > @id
insert @d(id, val, strVal)
Select @id, i1, s1 From sortTbl where id = @id union
Select @id, i2, s2 From sortTbl where id = @id union
Select @id, i3, s3 From sortTbl where id = @id union
Select @id, i4, s4 From sortTbl where id = @id union
Select @id, i5, s5 From sortTbl where id = @id
-- -------------------------------------------
Insert @s(id, val, strVal)
Select id, val, strVal from @d
order by val
Delete @d
end
Update s set colOrder =
(Select Count(*) from @s
Where id = s.id
and rn <= s.rn)
From @s s
Select a.Id,
c1.Val, c1.strVal,
c2.Val, c2.strVal,
c3.Val, c3.strVal,
c4.Val, c4.strVal,
c5.Val, c5.strVal
From sortTbl a
left join @s c1 on c1.id = a.id and c1.colOrder = 1
left join @s c2 on c2.id = a.id and c2.colOrder = 2
left join @s c3 on c3.id = a.id and c3.colOrder = 3
left join @s c4 on c4.id = a.id and c4.colOrder = 4
left join @s c5 on c5.id = a.id and c5.colOrder = 5