Dynamisk sortering i stored procedure

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?

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å.

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.

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?

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')

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'

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

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

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 :-)

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 :)

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

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 :)

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

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?

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.

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?

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

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

Det er vidst en gammel en den her.

Det endte med, at jeg brugte Lucene.net