Avatar billede chrisrj Forsker
29. september 2022 - 18:41 Der er 28 kommentarer og
3 løsninger

Fejl i stored procedure

Hejsa

Jeg har denne stored procedure, men jeg får intet data ud af den. Jeg er totalt noob når det kommer til stored procedures. :S

CREATE PROCEDURE [dbo].[GetFSRESGData] 
@page_size INT,
@page_num INT,
@vat_number NVARCHAR 
AS 
BEGIN 
SET NOCOUNT ON; 
; WITH RESULTS AS ( 
SELECT *, ROW_NUMBER()
OVER(ORDER BY vat_number DESC) AS rn,
ROW_NUMBER()
OVER(ORDER BY vat_number ASC) AS rn_reversed 
FROM FSRTest1
WHERE vat_number = @vat_number)
SELECT *,
CAST(rn + rn_reversed - 1 AS INT) AS total_rows,
CAST(CASE(rn + rn_reversed - 1) % @page_size WHEN 0
THEN(rn + rn_reversed - 1) / @page_size
ELSE((rn + rn_reversed - 1) / @page_size) + 1     
END AS INT) AS total_pages
FROM RESULTS a
WHERE a.rn BETWEEN 1 + ((@page_num - 1) * @page_size) AND @page_num *@page_size
ORDER BY rn ASC
END

Jeg kalder den således:
EXEC dbo.GetFSRESGData @page_size = 10, @page_num = 0, @vat_number = 'DK33860390'

Tabellen:
CREATE TABLE [dbo].[FSRtest1] (
    [vat_number]  NVARCHAR (50) NOT NULL,
    [label]      NVARCHAR (50) NULL,
    [value]      NVARCHAR (50) NULL,
    [unit]        NVARCHAR (50) NULL,
    [valid_from]  NVARCHAR (50) NULL,
    [valid_until] NVARCHAR (50) NULL
);


Og ja, der er naturligvis data der passer til testen. ;)

Er testet med alm. SELECT.
Avatar billede arne_v Ekspert
29. september 2022 - 18:53 #1
Der er flere ting som undrer mig.

CASE(rn + rn_reversed - 1) % @page_size WHEN 0
THEN(rn + rn_reversed - 1) / @page_size
ELSE((rn + rn_reversed - 1) / @page_size) + 1   
END

kan vel forsimples til:

(rn + rn_reversed - 1 + @page_size - 1) / @page_size
Avatar billede arne_v Ekspert
29. september 2022 - 18:57 #2
Og så tror jeg at det ville være simplete hvis du i.s.f. en mega select havde først en select som fandt antal rækker og antal sider og så en anden select som fandt de rækker som er i en bestemt side.
Avatar billede chrisrj Forsker
29. september 2022 - 19:05 #3
Ja, ok....

Kan du vise et eksempel? :)

Jeg er vildt rusten i page-halløj.
Avatar billede arne_v Ekspert
29. september 2022 - 19:23 #4
Jeg kan prøve at bixe noget senere.
Avatar billede chrisrj Forsker
29. september 2022 - 19:26 #5
Awesome, det vil jeg sætte stor pris på. :) :)
Avatar billede arne_v Ekspert
30. september 2022 - 02:03 #6
CREATE TABLE p (
    id INTEGER NOT NULL,
    cat CHAR(1),
    txt VARCHAR(10),
    PRIMARY KEY(id)
)
GO
INSERT INTO p VALUES (1, 'A', 'Record 1')
GO
INSERT INTO p VALUES (2, 'A', 'Record 2')
GO
INSERT INTO p VALUES (3, 'A', 'Record 3')
GO
INSERT INTO p VALUES (4, 'B', 'Record 4')
GO
INSERT INTO p VALUES (5, 'A', 'Record 5')
GO
INSERT INTO p VALUES (6, 'A', 'Record 6')
GO
INSERT INTO p VALUES (7, 'A', 'Record 7')
GO
INSERT INTO p VALUES (8, 'A', 'Record 8')
GO
INSERT INTO p VALUES (9, 'B', 'Record 9')
GO
INSERT INTO p VALUES (10, 'A', 'Record 10')
GO
INSERT INTO p VALUES (11, 'A', 'Record 11')
GO
INSERT INTO p VALUES (12, 'A', 'Record 12')
GO
SELECT * FROM p
GO
CREATE PROCEDURE getpag @pagsiz INTEGER, @pagnum INTEGER, @cat CHAR(1)
AS
BEGIN
    DECLARE @nrows INTEGER
    DECLARE @npags INTEGER
    DECLARE @firstrow INTEGER
    DECLARE @lastrow INTEGER
    SELECT @nrows = COUNT(*) FROM p WHERE cat = @cat
    SET @npags = (@nrows + @pagsiz - 1) / @pagsiz
    SET @firstrow = (@pagnum - 1) * @pagsiz + 1
    SET @lastrow = @pagnum * @pagsiz
    ;
    WITH temp AS (SELECT *,ROW_NUMBER() OVER(ORDER BY id ASC) AS rn FROM p WHERE cat = @cat)
    SELECT @pagnum AS currpage,@npags AS totalpages,* FROM temp WHERE rn BETWEEN @firstrow AND @lastrow
END
GO
EXEC getpag 4,1,'A'
GO
EXEC getpag 4,2,'A'
GO
EXEC getpag 4,3,'A'
GO
EXEC getpag 5,2,'A'
GO
DROP PROCEDURE getpag
GO
DROP TABLE p
GO


giver:

1> CREATE TABLE p (
2>    id INTEGER NOT NULL,
3>    cat CHAR(1),
4>    txt VARCHAR(10),
5>    PRIMARY KEY(id)
6> )
7> GO
1> INSERT INTO p VALUES (1, 'A', 'Record 1')
2> GO
(1 row affected)
1> INSERT INTO p VALUES (2, 'A', 'Record 2')
2> GO
(1 row affected)
1> INSERT INTO p VALUES (3, 'A', 'Record 3')
2> GO
(1 row affected)
1> INSERT INTO p VALUES (4, 'B', 'Record 4')
2> GO
(1 row affected)
1> INSERT INTO p VALUES (5, 'A', 'Record 5')
2> GO
(1 row affected)
1> INSERT INTO p VALUES (6, 'A', 'Record 6')
2> GO
(1 row affected)
1> INSERT INTO p VALUES (7, 'A', 'Record 7')
2> GO
(1 row affected)
1> INSERT INTO p VALUES (8, 'A', 'Record 8')
2> GO
(1 row affected)
1> INSERT INTO p VALUES (9, 'B', 'Record 9')
2> GO
(1 row affected)
1> INSERT INTO p VALUES (10, 'A', 'Record 10')
2> GO
(1 row affected)
1> INSERT INTO p VALUES (11, 'A', 'Record 11')
2> GO
(1 row affected)
1> INSERT INTO p VALUES (12, 'A', 'Record 12')
2> GO
(1 row affected)
1> SELECT * FROM p
2> GO
id          cat  txt
----------- ---- ----------
          1 A    Record 1
          2 A    Record 2
          3 A    Record 3
          4 B    Record 4
          5 A    Record 5
          6 A    Record 6
          7 A    Record 7
          8 A    Record 8
          9 B    Record 9
          10 A    Record 10
          11 A    Record 11
          12 A    Record 12

(12 rows affected)
1> CREATE PROCEDURE getpag @pagsiz INTEGER, @pagnum INTEGER, @cat CHAR(1)
2> AS
3> BEGIN
4>    DECLARE @nrows INTEGER
5>    DECLARE @npags INTEGER
6>    DECLARE @firstrow INTEGER
7>    DECLARE @lastrow INTEGER
8>    SELECT @nrows = COUNT(*) FROM p WHERE cat = @cat
9>    SET @npags = (@nrows + @pagsiz - 1) / @pagsiz
10>    SET @firstrow = (@pagnum - 1) * @pagsiz + 1
11>    SET @lastrow = @pagnum * @pagsiz
12>    ;
13>    WITH temp AS (SELECT *,ROW_NUMBER() OVER(ORDER BY id ASC) AS rn FROM p W
HERE cat = @cat)
14>    SELECT @pagnum AS currpage,@npags AS totalpages,* FROM temp WHERE rn BET
WEEN @firstrow AND @lastrow
15> END
16> GO
1> EXEC getpag 4,1,'A'
2> GO
currpage    totalpages  id          cat  txt        rn
----------- ----------- ----------- ---- ---------- --------------------
          1          3          1 A    Record 1                      1
          1          3          2 A    Record 2                      2
          1          3          3 A    Record 3                      3
          1          3          5 A    Record 5                      4

(4 rows affected)
1> EXEC getpag 4,2,'A'
2> GO
currpage    totalpages  id          cat  txt        rn
----------- ----------- ----------- ---- ---------- --------------------
          2          3          6 A    Record 6                      5
          2          3          7 A    Record 7                      6
          2          3          8 A    Record 8                      7
          2          3          10 A    Record 10                    8

(4 rows affected)
1> EXEC getpag 4,3,'A'
2> GO
currpage    totalpages  id          cat  txt        rn
----------- ----------- ----------- ---- ---------- --------------------
          3          3          11 A    Record 11                    9
          3          3          12 A    Record 12                    10

(2 rows affected)
1> EXEC getpag 5,2,'A'
2> GO
currpage    totalpages  id          cat  txt        rn
----------- ----------- ----------- ---- ---------- --------------------
          2          2          7 A    Record 7                      6
          2          2          8 A    Record 8                      7
          2          2          10 A    Record 10                    8
          2          2          11 A    Record 11                    9
          2          2          12 A    Record 12                    10

(5 rows affected)
1> DROP PROCEDURE getpag
2> GO
1> DROP TABLE p
2> GO


Eksemplet er naturligvis ikke helt magen til dine data, men jeg mener at den her måde at strukturere SP på gør at man kan læse den.
Avatar billede chrisrj Forsker
30. september 2022 - 12:07 #7
Fedt, tak!

Så hvis jeg "oversætter" det til mine data - ser dette så korrekt ud så vidt du kan se? :D

CREATE PROCEDURE GetFSRESGData @pagsiz INT, @pagnum INT, @vat_number nvarchar
AS
BEGIN
    DECLARE @nrows INT
    DECLARE @npags INT
    DECLARE @firstrow INT
    DECLARE @lastrow INT
    SELECT @nrows = COUNT(*) FROM FSRTest1 WHERE vat_number = @vat_number
    SET @npags = (@nrows + @pagsiz - 1) / @pagsiz
    SET @firstrow = (@pagnum - 1) * @pagsiz + 1
    SET @lastrow = @pagnum * @pagsiz
    ;
    WITH temp AS (SELECT *,ROW_NUMBER() OVER(ORDER BY vat_number  ASC) AS rn FROM FSRTest1 WHERE vat_number = @vat_number )
    SELECT @pagnum AS currpage,@npags AS totalpages,* FROM temp WHERE rn BETWEEN @firstrow AND @lastrow
END


Hmmm, jeg får ikke noget ud når jeg tester med denne. :-/

EXEC GetFSRESGData 4,1,'DK12345678'
Avatar billede arne_v Ekspert
30. september 2022 - 15:18 #8
Tid for noget debug.

[div]
;
[div]
->
[div]
SELECT @nrows, @npags,@firstrow,@lastrow
;
[div]

og se hvilke værdier den finder.
Avatar billede arne_v Ekspert
30. september 2022 - 15:19 #9
Glemte / paa afslutnings mark, men det kan laeses.
Avatar billede chrisrj Forsker
30. september 2022 - 15:27 #10
Hmmm, jeg gik ud fra, at du mente jeg skulle gøre sådan her?

CREATE PROCEDURE GetFSRESGData @pagsiz INT, @pagnum INT, @vat_number nvarchar
AS
BEGIN
    DECLARE @nrows INT
    DECLARE @npags INT
    DECLARE @firstrow INT
    DECLARE @lastrow INT
    SELECT @nrows, @npags,@firstrow,@lastrow
;
    WITH temp AS (SELECT *,ROW_NUMBER() OVER(ORDER BY vat_number ASC) AS rn FROM FSRTest1 WHERE vat_number = @vat_number )
    SELECT @pagnum AS currpage,@npags AS totalpages,* FROM temp WHERE rn BETWEEN @firstrow AND @lastrow
END


Samme EXEC:
EXEC GetFSRESGData 4,0,'DK33860390'

Giver dette:

    (1 row(s) affected)
   

    (0 row(s) affected)
Avatar billede arne_v Ekspert
30. september 2022 - 15:43 #11
Nej.

Beregningerne skulle ikke tages ud.

    SELECT @nrows = COUNT(*) FROM FSRTest1 WHERE vat_number = @vat_number
    SET @npags = (@nrows + @pagsiz - 1) / @pagsiz
    SET @firstrow = (@pagnum - 1) * @pagsiz + 1
    SET @lastrow = @pagnum * @pagsiz
SELECT @nrows, @npags,@firstrow,@lastrow
    ;
Avatar billede arne_v Ekspert
30. september 2022 - 15:45 #12
Men jeg undrer mig alligevel over at der ikke bliver udskrevet noget.

Redirecter du results til et sted?
Avatar billede chrisrj Forsker
30. september 2022 - 15:45 #13
Ah, ok. :)

Så giver den 0,0,1,4
Avatar billede arne_v Ekspert
30. september 2022 - 15:46 #14
Hvordan sende du EXEC?

Jeg testede med OSQL command line utility, men det er fordi jeg er meget gammeldags.
Avatar billede chrisrj Forsker
30. september 2022 - 15:47 #15
Hehe, jeg bruger Visual studio 2022.
Avatar billede arne_v Ekspert
30. september 2022 - 15:58 #16
Saa er vi jo paa sporet.

SELECT @nrows = COUNT(*) FROM FSRTest1 WHERE vat_number = @vat_number

saetter @nrows til 0.

Det maa der jo vaere en grund til.

Er der fejl i det VAT nummer du kalder med sammenhold med det rigtige format i databasen?
Avatar billede arne_v Ekspert
30. september 2022 - 16:00 #17
Er der trailing spaces i feltet i databasen? Er 'DK' prefix i databasen? Er det nummer du tester med faktisk i databasen? Etc..
Avatar billede chrisrj Forsker
30. september 2022 - 16:03 #18
#16: Det er kopieret direkte fra tabellen, så det ville da undre mig... :)

#17: Nej, nej*, ja

* Altså det ER med, men det er ikke noget databasen blander sig i - det er ren data.
Avatar billede arne_v Ekspert
30. september 2022 - 16:11 #19
Hvad datatype er vat_number i databasen?

Kan du proeve at bruge den eksakt samme data type i SP?
Avatar billede chrisrj Forsker
30. september 2022 - 16:15 #20
Som skrevet i opslaget... ;P er min tabel som således:

CREATE TABLE [dbo].[FSRtest1] (
    [vat_number]  NVARCHAR (50) NOT NULL,
    [label]      NVARCHAR (50) NULL,
    [value]      NVARCHAR (50) NULL,
    [unit]        NVARCHAR (50) NULL,
    [valid_from]  NVARCHAR (50) NULL,
    [valid_until] NVARCHAR (50) NULL
);


I SP'ern skriver jeg @vat_number nvarchar


Nu prøvede jeg at rette det til @vat_number nvarchar (50), og sørme om der ikke kommer data ud nu!! :D
Avatar billede chrisrj Forsker
30. september 2022 - 16:17 #21
Jeg kunne dog godt undvære cumpage, totalpages og m kolonnerne i udtrækket... :)
Avatar billede arne_v Ekspert
30. september 2022 - 16:18 #22
Default er sikkert (1) og saa har den trunkeret det angivne til 'D' og saa var der ingen matches.

Vi ville have opdaget det med en:

SELECT @vat_number
Avatar billede arne_v Ekspert
30. september 2022 - 16:20 #23
Men der maa vaere hul igennem nu.

Saa skal debug SELECT erne fjernes igen.

Og du skal evt. lige have styr paa om foerste side er 1 eller 0. Mit eksempel antog 1.
Avatar billede arne_v Ekspert
30. september 2022 - 16:24 #24
SELECT @pagnum AS currpage,@npags AS totalpages,* FROM temp WHERE rn BETWEEN @firstrow AND @lastrow

kan jo rettes til:

SELECT * FROM temp WHERE rn BETWEEN @firstrow AND @lastrow

eller

SELECT felt1,felt2,felt3 FROM temp WHERE rn BETWEEN @firstrow AND @lastrow

efter behov.

Men har du ikke brug for total page for at kunne lave noget navigation?

Hvis ikke behoever du jo slet ikke beregne totalpages.
Avatar billede chrisrj Forsker
30. september 2022 - 16:27 #25
#22 Ja, det ser sådan ud. :)

#23 Jaw jaw.

Jaeh...
Jeg har fjernet denne

SELECT @nrows, @npags,@firstrow,@lastrow

Men det er ikke nok - hvad skal jeg ellers fjerne? :D


...sad også og overvejede om den var 0 eller 1 baseret. :D
Avatar billede arne_v Ekspert
30. september 2022 - 16:27 #26
Når du bruger VS så antager jeg at du er mere til C# end til T-SQL.

Er du opmærksom på at du kan lave SP i C# fremfor T-SQL?
Avatar billede arne_v Ekspert
30. september 2022 - 16:29 #27
Der var vis kun 1 debug SELECT, så hvis du får det output du skal, så ...
Avatar billede chrisrj Forsker
30. september 2022 - 16:33 #28
#24 nå vi krydsede lige. :) Så fik jeg fjernet de første, tak for det. :) m kolonnen er der dog stadig.

"Men har du ikke brug for total page for at kunne lave noget navigation?

Hvis ikke behoever du jo slet ikke beregne totalpages."
Til at starte med skal vi bare have noget der virker. Så må nice-to-have komme bagefter. Nu bliver bossmanden i hvert fald lidt mindre stresset. :)


#25 Jeg ved ikke engang hvad T-SQL står for. ;P
Avatar billede arne_v Ekspert
30. september 2022 - 16:38 #29
T-SQL er SQLServers SQL dialekt.

D.v.s. standard SQL med SELECT, INSERT etc. og så SQLServer specifik syntax for definering af stored procedures etc..
Avatar billede arne_v Ekspert
30. september 2022 - 16:41 #30
Avatar billede chrisrj Forsker
30. september 2022 - 16:46 #31
Aha. Tak for info. :)

Anyway, jeg må vist hellere lukke og give credit til din løsning. :)

Endnu engang mange tak for hjælpen. En af dagene må jeg sgu indstille dig til en fortjenstmedalje hos dronningen. ;P
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