Avatar billede rbl Praktikant
24. januar 2010 - 23:17 Der er 18 kommentarer og
1 løsning

Dynamisk sortering i stored procedure

Hejsa

Jeg skal lave noget dynamisk sortering i en stored procedure.

Jeg fandt denne side på nettet (Example 3):
http://www.mssqltips.com/tip.asp?tip=1455

Der står hvordan man laver dynamisk sortering, men jeg kan ikke rigtig få det til at virke.

Her er min sortering:

        ORDER BY 
    CASE @sortdirection
    WHEN 1 THEN
        case when @OrderBy = 1 then FullName
            when @OrderBy = 2 then Price
            when @OrderBy = 3 then CreatedDate
            end
    END 
    ASC,
    CASE @sortdirection
    WHEN 2 THEN
        case when @OrderBy = 1 then FullName
        when @OrderBy = 2 then Price
            when @OrderBy = 3 then CreatedDate
            end
    END
    DESC

Der er ikke syntaxfejl, så jeg kan godt gemme den stored procedure. Problemet opstår, når jeg kører den med @OrderBy = 1. Der får jeg fejlen: "Conversion failed when converting datetime from character string".

@OrderBy = 2 og @OrderBy = 3 virker fint.

Hvis jeg udkommenterer de 2 linier med "CreatedDate", får jeg samme fejl, bare med Konvertering fra char til Money (Price er af typen Money).

Hvis jeg udkommenterer de 4 linier med "CreatedDate" og "Price", virker @OrderBy = 1 lige pludselig. Men så er sorteringen jo ubrugelig...

Hvad er det lige der sker?

FullName er varchar(255)
Avatar billede janus_007 Nybegynder
24. januar 2010 - 23:26 #1
Du skal ikke gøre den slags i en sproc, det er slet ikke tanken med sprocs :) -Virkeligt misbrug du har gang i :-|

Når det så er sagt, så kan du altid debugge din sprocs ved at indsætte :

print @statement
return

Gør det efter din string er opbygget og inden du eksekverer den.
Og på den måde se hvordan din order by ser ud.

Har du en TOP i din sql?
Avatar billede rbl Praktikant
24. januar 2010 - 23:37 #2
Nej, ingen TOP.

Det er ikke en streng, jeg opbygger. Det er en komplet sql-query - som i eksemplet fra linket.

Desuden er en CASE WHEN ikke misbrug. Det er meget brugt for at kunne gøre sql dynamisk uden at bygge en streng op.

Det at bygge en streng op og eksekvere den er en forældet, langsom og rodet måde at lave stored procedures på.
Avatar billede janus_007 Nybegynder
24. januar 2010 - 23:48 #3
Helt enig.. sådan skal man nu heller ikke gøre mht. dynamisk sproc.

Jeg synes nu stadig ikke man skal lave en case when, men smag og behag, specielt når du ikke har nogen TOP kan du uden problemer sortere i applikationen,

Jeg kiggede lige på linket der.. Har du prøvet at lave en convert på datetime.

Convert(varchar(25), CreatedDate), jeg formoder at sproc'en bliver recompilet hver gang og derved prøver at lave en queryplan på mulige input.
Avatar billede rbl Praktikant
25. januar 2010 - 00:02 #4
Ok, jeg bruger noget andet end TOP. Jeg bruger en cte og noget RowNumber, så jeg kan lave paging

Jeg har prøvet med den convert, som du foreslår, men så får jeg en syntaxfejl "Incorrect syntax near ','" :-)

Desuden, hvordan sorterer den på en dato, der er konverteret til varchar?
Avatar billede janus_007 Nybegynder
25. januar 2010 - 00:52 #5
Så blev jeg lige nødt til at lave en lille test :)

Here you go:
alter PROCEDURE dbo.getCustomerData @sortby VARCHAR(9)
AS
SET nocount ON

SELECT* FROM dbo.MyTest
ORDER BY
      CASE @sortby
      WHEN 'firstname' THEN F1
      WHEN 'datecreated' THEN isnull(nullif(@sortby, datecreated), DateCreated )
  END


EXEC dbo.getCustomerData 'datecreated'


create table MyTest
(
Id int,
F1 varchar(25),
DateCreated datetime
)
insert into MyTest values(1, 'Peter', '2010-10-25')
insert into MyTest values(2, 'Jens', '2010-05-12')
Avatar billede janus_007 Nybegynder
25. januar 2010 - 00:55 #6
hov *S*

Sådan her:
alter PROCEDURE dbo.getCustomerData @sortby VARCHAR(25)
AS
SET nocount ON

SELECT* FROM dbo.MyTest
ORDER BY
      CASE @sortby
      WHEN 'firstname' THEN F1
      WHEN 'datecreated' THEN isnull(nullif(@sortby, 'datecreated'), DateCreated )
  END
asc

EXEC dbo.getCustomerData 'datecreated'
Avatar billede rbl Praktikant
25. januar 2010 - 22:44 #7
Hejsa

Beklager at jeg først svarer nu. Jeg har haft en travl dag. Jeg sætter pris på, at du gider hjælpe mig.

Jeg kan ikke helt forstå hvad der sker i den linie:
WHEN 'datecreated' THEN isnull(nullif(@sortby, 'datecreated'), DateCreated )

Jeg kalder mine variabler og værdier noget lidt andet, så jeg har forsøgt at erstatte med dem jeg bruger:
WHEN 1 THEN isnull(nullif(@OrderBy, 1), CreatedDate)

Men jeg får diverse fejl. Kan du ikke prøve at forklare mig hvad de forskellige ting i den linie gør?

Mvh
Rasmus
Avatar billede janus_007 Nybegynder
26. januar 2010 - 00:10 #8
Den sætter Order By til null, hvis @sortby ikke = 'datecreated', sagt på en anden måde, først nuller den hvis @sortby = 'datecreated' og hvis den er null er den gyldig og sætter Order By til DateCreated.

I dit tilfælde vil det se sådan her ud:

    ORDER BY
    CASE @sortdirection
    WHEN 1 THEN
        case when @OrderBy = 1 then FullName
            when @OrderBy = 2 then Price
            when @OrderBy = 3 then isnull(nullif(@OrderBy, 3), CreatedDate)
            end
    END
    ASC,
    CASE @sortdirection
    WHEN 2 THEN
        case when @OrderBy = 1 then FullName
        when @OrderBy = 2 then Price
            when @OrderBy = 3 then isnull(nullif(@OrderBy, 3), CreatedDate)
            end
    END
    DESC
Avatar billede rbl Praktikant
26. januar 2010 - 20:37 #9
Jeg kan ikke rigtig få det til at virke. Den kommer med en fejl:
"Implicit conversion from datetime to int is not allowed"


Det ender med, at jeg laver 2 forskellige stored procedures :-)
Avatar billede janus_007 Nybegynder
26. januar 2010 - 20:41 #10
Jeg ved det virker, har netop specielt udviklet det til dig og testet det af :)

Lav en formindsket version og post her, så ser vi på det sammen, jeg er sikker på du bare skal bytte om på et par simple udtryk :)
Avatar billede rbl Praktikant
26. januar 2010 - 20:59 #11
Ok, her er en simplificeret version (Jeg har fjernet nogle joins + where-klausulen):

declare @Page int, @Count int, @OrderBy int, @sortdirection int
set @Page = 1
set @Count = 10
set @OrderBy = 2
set @sortdirection = 1;

with cte as
(
select ROW_NUMBER() OVER(
ORDER BY Id  asc) AS RowNumber, count(*) over() as MaxNum, Product_joined.*  from Product_joined
)

select * from cte where RowNumber > ((@Page-1) * @Count) AND RowNumber <= (@Page * @Count)

ORDER BY
    CASE @sortdirection
    WHEN 1 THEN
        case when @OrderBy = 1 then FullName
            when @OrderBy = 2 then Price
            when @OrderBy = 3 then isnull(nullif(@OrderBy, 3), CreatedDate)
            end
    END
    ASC,
    CASE @sortdirection
    WHEN 2 THEN
        case when @OrderBy = 1 then FullName
        when @OrderBy = 2 then Price
            when @OrderBy = 3 then isnull(nullif(@OrderBy, 3), CreatedDate)
            end
    END
    DESC
Avatar billede janus_007 Nybegynder
26. januar 2010 - 23:39 #12
hmmm, sært, det viser sig at

Declare din @OrderBy som varchar :)

declare @Page int, @Count int, @OrderBy varchar(1), @sortdirection int
set @Page = 1
set @Count = 10
set @OrderBy = '1'
set @sortdirection = 1;
...
case
            when @OrderBy = '1' then FullName
            when @OrderBy = '2' then isnull(nullif(@OrderBy, 2), Id)
            when @OrderBy = '3' then isnull(nullif(@OrderBy, 3), CreatedDate)
            end


Så virker det, lidt besværligt men det funker da :)
Avatar billede janus_007 Nybegynder
26. januar 2010 - 23:41 #13
case
            when @OrderBy = '1' then FullName
            when @OrderBy = '2' then isnull(nullif(@OrderBy, '2'), Id)
            when @OrderBy = '3' then isnull(nullif(@OrderBy, '3'), CreatedDate)
            end
Avatar billede rbl Praktikant
27. januar 2010 - 21:32 #14
Ok, nu virker sortering på FullName, men det gør sortering på Price så ikke:

"Insufficient result space to convert a money value to varchar".

Jeg kom til at tænke på noget. Du sagde i starten, at du ikke synes man skulle bruge CASE i en stored procedure. Hvordan ville du løse det uden at bruge CASE?
Avatar billede janus_007 Nybegynder
28. januar 2010 - 10:59 #15
Har du brugt denne:
when @OrderBy = '2' then isnull(nullif(@OrderBy, '2'), Id)

Og omskrevet den til:
when @OrderBy = '2' then isnull(nullif(@OrderBy, '2'), Price)

Jeg kom til at tænke på noget. Du sagde i starten, at du ikke synes man skulle bruge CASE i en stored procedure. Hvordan ville du løse det uden at bruge CASE?

Nu fortalte du mig at du brugte paging og derved vil du formentlig kalde sproc'en fra en applikation...
Jo mere jeg har arbejdet med SQL igennem diverse applikationer, jo mindre synes jeg om sprocs til data udtræk. Misforstå mig ikke, jeg elsker T-sql og alt hvad SQL-Serveren ellers kan byde på, men sprocs er i min verden noget der skal holdes på serveren og ikke i applikationen. Det har til gengæld taget mig næsten 10år at komme frem til den overbevisning, herefter blev mit liv meget nemmere :)
Nogle gange gør man tingene unødigt kompliceret :)

Man kan sagtens lave et godt og stabilt DAL med SQL og nu efter vi er blevet beriget med Linq er det blevet endnu nemmere.
Jeg har set et utal af applikationer med diverse abstraktioner, den typiske og mest sære af dem alle er en data abstraktion igennem et DAL som kommunikerer med sprocs.
Avatar billede rbl Praktikant
28. januar 2010 - 12:32 #16
Ja, jeg erstattede Id med Price. Jeg gik ud fra, at det var det, du mente.

Hvis man bruger et datalag til at søge og sortere med, bliver der så ikke en masse overhead med alt så meget data, der bliver transporteret fra sql til datalag?

Alternativt skal jeg bruge noget linq, men så går jeg glip af hurtigheden i stored procedures. Har du erfaring med, om linq kører lige så hurtigt?
Avatar billede janus_007 Nybegynder
29. januar 2010 - 00:37 #17
hej rbl

Tanken med datalaget vil i dette tilfælde være at konstruere sine select-statements der, altså i ren SELECT ... FROM..., på den måde kan de laves lige så fleksible som man lyster. Når man så vælger denne tilgang så syntes jeg helt sikkert man bør arbejde med interfaces på dal'en :)

Jeg ville dog ikke anbefale ovenstående, hvis man har Linq til rådighed :)

Mht. Linq og sprocs vil hastigheden i mange tilfælde falde ud til Linq's fordel, på samme måde som Sql oftest også performer bedre end en sproc.
Som jeg skrev tidligere så er jeg totalt hooked på Linq, det er blevet så meget nemmere at tilgå data og i dag ville jeg have meget svært ved at undvære Linq To Sql/ Linq To Entities. Det kræver naturligvis man ved hvad man laver og nogle gange skal man så skrive sit Linq udtryk om, men man sidder jo også og fifler med T-sql når udtrækket ikke performer optimalt.

Anyway... Vi taler virkelig minimale forskelle som ligger gemt i om hvorvidt query optimizeren kan genbruge sin queryplan eller ej og om sproc'en bliver recompilet! I dit tilfælde med en CASE vil den ikke kunne genbruge query planen. Men som sagt... det er virkelig petitesser i 99% af tilfældende, nogle gange går det virkelig galt med performance time i en sproc.

Læs mere om det her: http://technet.microsoft.com/en-us/library/cc966425.aspx
Avatar billede rbl Praktikant
24. februar 2010 - 10:34 #18
Hejsa

Jeg fik det aldrig til at virke ordentligt, så jeg har lavet 3 næsten ens stored procedures.

Så må jeg sætte mig ind i LINQ på et senere tidspunkt.
Men tak fordi du brugte tid til at hjælpe mig.

Vil du have pointene? så læg et svar
Avatar billede rbl Praktikant
09. november 2011 - 08:19 #19
Det er vidst en gammel en den her.

Det endte med, at jeg brugte Lucene.net
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester