Avatar billede dreyfusdk Nybegynder
23. oktober 2005 - 01:18 Der er 12 kommentarer

Datoer i Stored Procedure

Jeg har lidt problemer med sortering af udtræk i MS SQL med datoer. Jeg er lidt en newbie med Stored Procedure, men eller ganske vant til SQL.

startDate og endDate er i databasen af typen "DateTime".


<code>

ALTER PROCEDURE statisticRevenue
    @startDate AS datetime,
    @endDate AS datetime
AS
   
    SELECT SUM(totalPrice) AS revenue FROM contract WHERE (created BETWEEN @endDate AND @endDate)
   
RETURN

</code>

I hvilket format skal jeg poste datoerne når jeg vil execute sqlen? Sådan som forspørgslen er nu får jeg revenue som NULL.
Avatar billede xanadu Nybegynder
23. oktober 2005 - 03:13 #1
Hej dreyfusdk,

det afhænger af følgende: om du bruger parameters-collectionen til at poste datoerne til den stored procedure eller inline-sql, samt opsætningen af datoformatet i databasen.

Hvis du bruger inline-sql (som jeg kalder den slags, dvs. et kald på én linie med parametrene indlejret som strings), er det sikreste at bruge det såkaldte militæriske format eller EU-format, YYYY-MM-DD, det skulle SQL Server ikke kunne tage fejl af:
EXEC statisticRevenue '2005-01-01', '2005-12-31' -- for hele år 2005

Hvis du bruger Parameters-collectionen, f. eks. fra ASP.NET, sørger frameworket for at poste datoerne i det korrekte format, det er derfor mest sikkert (og umuliggør samtidig injection-attacker):

SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "statisticsRevenue";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@startDate", SqlDbType.DateTime).Value = startDate; // hvor startDate er af typen DateTime
cmd.Parameters.Add("@endDate", SqlDbType.DateTime).Value = endDate; // hvor endDate er af typen DateTime

Håber det hjalp. Happy SQLing,

Xanadu
Avatar billede dreyfusdk Nybegynder
23. oktober 2005 - 11:35 #2
Hej Xanadu,

Pt. forgår udviklingen i Visual Studio, så det endnu ikke på implementerings-stadiet - det eksempel er du kom med er ellers meget godt (koder selv asp så får nok brug for det på et tidspunkt). Men i lige præcis dette tilfælde skal jeg i øvrigt sige, at det er til en Java applikation.

Jeg har postet lidt screenshots som visualiserer det problem jeg har... Garanteret total dummyfejl, men sådan er det ofte når man påbegynder et nyt område.

Min stored procedure (koden):
http://www.programmoer.dk/udvikling/storedprocedure_statisticRevenue.png

Execute boksen hvor jeg indtaster værdier:
http://www.programmoer.dk/udvikling/storedprocedure_run_statisticRevenue.png

Mit output (bemærk "null" selvom der ligger værdier indenfor grænsen"):
http://www.programmoer.dk/udvikling/storedprocedure_output_statisticRevenue.png

Hilsen, Magnus
Avatar billede arne_v Ekspert
23. oktober 2005 - 11:58 #3
i Java kan du bruge PreparedStatement som har samme funktionalitet som
parameters i .NET
Avatar billede arne_v Ekspert
23. oktober 2005 - 11:59 #4
rettelse - med en stored procedure skal du bruge en subklasse af PreparedStatement
ved navn CallableStatement
Avatar billede arne_v Ekspert
23. oktober 2005 - 12:01 #5
med hensyn til din fejl - så prøv og kør den med * og se hvilke rækker der kommer ud
Avatar billede dreyfusdk Nybegynder
23. oktober 2005 - 12:16 #6
arne_v>

1: Tak for tipsne til Java!

2: Der bliver ikke returneret nogen rækker når det er jeg kører med *... Suk. Har du evt. nogen eksempler til at ligge på stored procedures der arbejder med dato?
Avatar billede arne_v Ekspert
23. oktober 2005 - 12:53 #7
jeg kan vel lave et eksempel !
Avatar billede arne_v Ekspert
23. oktober 2005 - 13:13 #8
SELECT SUM(totalPrice) AS revenue FROM contract WHERE (created BETWEEN @endDate AND @endDate)
   
skal nok være

SELECT SUM(totalPrice) AS revenue FROM contract WHERE (created BETWEEN @startDate AND @endDate)

:-)
Avatar billede arne_v Ekspert
23. oktober 2005 - 13:13 #9
CREATE TABLE tdt (
    id INTEGER PRIMARY KEY,
    val DECIMAL(10,2),
    dt DATETIME
)
GO

INSERT INTO tdt VALUES(1, 123.45, CAST('2005-10-23' AS DATETIME))
GO
INSERT INTO tdt VALUES(2, 678.90, CAST('2005-10-25' AS DATETIME))
GO

SELECT * FROM tdt
GO

CREATE PROCEDURE uspdt(@startDate AS datetime,@endDate AS datetime)
AS
SELECT SUM(val) AS sumval FROM tdt WHERE dt BETWEEN @startDate AND @endDate
GO
Avatar billede arne_v Ekspert
23. oktober 2005 - 13:13 #10
package october;

import java.sql.*;
import java.text.*;

public class USPDT {
    public static void main(String[] args) throws Exception {
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost;DatabaseName=Test", "sa", "");
        con.setCatalog("Test");
        CallableStatement cstmt = con.prepareCall("{CALL uspdt(?,?)}");
        cstmt.setDate(1, new Date(df.parse("2005-10-01").getTime()));
        cstmt.setDate(2, new Date(df.parse("2005-10-31").getTime()));
        cstmt.execute();
        ResultSet rs = cstmt.getResultSet();
        if(rs.next()) {
            System.out.println(rs.getBigDecimal(1));
        }
        cstmt.close();
        con.close();
    }
}
Avatar billede arne_v Ekspert
23. oktober 2005 - 13:14 #11
output:

802.35

hvilket ser rigtigt ud
Avatar billede arne_v Ekspert
31. december 2005 - 15:34 #12
All set ?
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