[Stored Proc] waarde uitlezen

[Jaspertje]

Goiemorgen allemaal..

Ik ben bezig om een stored procedure te schrijven in SQL Server 2000. Ik ben (denk ik al een heel eind gekomen, alleen zit ik nu vast. Dit is de eerste sp die ik schrijf (behalve de test die ik net gedaan heb om te kijken of ik het kan ). Ik heb gezocht op het internet.. maar kwam niet echt eruit.

De volgende SP:

code:

CREATE PROCEDURE sp_GetTotaalEndOfWeek
    @User_id int,
    @Datum datetime
AS BEGIN
    DECLARE @StartKosten decimal
    DECLARE @Inkomsten decimal
    DECLARE @Uitgaven decimal
    DECLARE @Totaal decimal

Select @StartKosten = start_kas + start_bank From tbl_start where user_id = @User_id
Select @Inkomsten = SUM(inkomsten_bedrag) From tbl_inkomsten where user_id = @User_id AND Datum < @Datum
Select @Uitgaven = SUM(kosten) FROM tbl_uitgaven where user_id = @User_id AND Datum < @Datum

END
Return @StartKosten+@Inkomsten+@Uitgaven
GO

Wat de bedoeling is, is om de drie waardes op te tellen en een bedrag terug te krijgen. De drie queries doen het wel appart. Maar of het op deze manier werkt weet ik niet. Waar volgens mij de fout zit, is bij het optellen "Return @StartKosten+@Inkomsten+@Uitgaven" dit stukje. Ik heb al geprobeerd om er bla = voor te zetten dus: "Return bla = @StartKosten+@Inkomsten+@Uitgaven" of : "Return @StartKosten+@Inkomsten+@Uitgaven AS Bla".. Maar beide hadden geenb succes

Als ik dan de syntyax check dan is hert niet goed... kan iemand mij op weg helen door een goede tutorial aan te rijken of te zeggen waar het fout gaat?

Tnx

[gorgi_19]

SQL:

CREATE PROCEDURE sp_GetTotaalEndOfWeek
    @User_id int,
    @Datum datetime,
                @TotaleWaarde decimal Output
AS BEGIN
    DECLARE @StartKosten decimal
    DECLARE @Inkomsten decimal
    DECLARE @Uitgaven decimal
    DECLARE @Totaal decimal

Select @StartKosten = start_kas + start_bank From tbl_start where user_id = @User_id
Select @Inkomsten = SUM(inkomsten_bedrag) From tbl_inkomsten where user_id = @User_id AND Datum < @Datum
Select @Uitgaven = SUM(kosten) FROM tbl_uitgaven where user_id = @User_id AND Datum < @Datum

END
Select @TotaleWaarde =  @StartKosten+@Inkomsten+@Uitgaven
GO

Iets in deze trant kan je dan gaan kijken.

[ Voor 5% gewijzigd door gorgi_19 op 16-04-2003 10:00 ]

[whoami]

Welke fout krijg je dan?

Volgens mij kan een SP geen waarde returnen. Misschien moet je return wel vervangen door select.

(Net zoiets als gorgi zegt dus).

[ Voor 81% gewijzigd door whoami op 16-04-2003 10:02 ]

[Jaspertje]

Het gaat nog steeds niet goed.. maar ik heb nu wat andere vraagjes:

- Kan ik die drie selects die gebeueren op die manier bij elkaar optellen of moet ik nog meer specificeren:

SQL:

Select @StartKosten = start_kas + start_bank AS temp From tbl_start where user_id = @User_id

as temp staat er nu bij en dan in de volgende aanroep @StartKosten(temp) gebruiken?? of maakt dat niks uit

- En wat moet ik nu in mn SQL als parameter aan die output meegeven, gewoon 0??

[gorgi_19]

Jaspertje schreef op 16 April 2003 @ 10:22:
Het gaat nog steeds niet goed.. maar ik heb nu wat andere vraagjes:

- Kan ik die drie selects die gebeueren op die manier bij elkaar optellen of moet ik nog meer specificeren:

SQL:

Select @StartKosten = start_kas + start_bank AS temp From tbl_start where user_id = @User_id

as temp staat er nu bij en dan in de volgende aanroep @StartKosten(temp) gebruiken?? of maakt dat niks uit

- En wat moet ik nu in mn SQL als parameter aan die output meegeven, gewoon 0??

Een voorbeeldje in VB.net:

Visual Basic .NET:

      Public Function ValidateUser(ByVal _user As BaseUser) As Integer Implements IDataProviderBase.ValidateUser

            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(Globals.ConnectionString)
            Dim myCommand As New SqlCommand("ProSim_ValidateUser", myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterUserName As New SqlParameter("@UserName", SqlDbType.NVarChar, 50)
            parameterUserName.Value = _user.UserName
            myCommand.Parameters.Add(parameterUserName)

            Dim parameterPassword As New SqlParameter("@Password", SqlDbType.NVarChar, 100)
            parameterPassword.Value = _user.Password
            myCommand.Parameters.Add(parameterPassword)

            Dim parameterGameID As New SqlParameter("@GameID", SqlDbType.NVarChar, 100)
            parameterGameID.Value = _user.GameID
            myCommand.Parameters.Add(parameterGameID)

            Dim parameterUserId As New SqlParameter("@UserID", SqlDbType.Int)
            parameterUserId.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterUserId)

            ' Execute the command in a try/catch to catch duplicate username errors
            Try
                ' Open the connection and execute the Command
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                If Not parameterUserId.Value Is Nothing And Not parameterUserId.Value Is System.DBNull.Value Then
                    Return parameterUserId.Value
                Else
                    Return -1
                End If

            Catch e As SqlException

                Throw e

            Catch e As Exception
                HttpContext.Current.Trace.Write("Login", e.Message)
                HttpContext.Current.Trace.Write("Login", e.Source)
                Return -1
            Finally
                ' Close the Connection
                If myConnection.State = ConnectionState.Open Then
                    myConnection.Close()
                End If
            End Try
            Return -1
        End Function

En de SP zelf:

SQL:

(
    @UserName Varchar(50),
    @Password Varchar(32),
    @GameID int,
    @UserID int Output
)
AS
Select 
    @UserID=UserID 
FROM 
    ProSim_User 
WHERE 
    UserName=@Username AND 
    Password=@Password AND 
    GameID=@GameID

Ik geeft dus een direction mee, waarna ik hem in de code kan uitlezen..

[ Voor 7% gewijzigd door gorgi_19 op 16-04-2003 10:29 ]

[Jaspertje]

Hmm juist, van zulke voorbeelden vond ik wel meer.. het enige jammer is, dat ik het niet op die manier doe.. maar op deze:

ASP:

SQL = "Exec GetTotaalEndOfWeek " & Session("StringNR") & ", '" & ChangeDate(BeginDate) & "', 0"
    Response.Write SQL
    'Response.end
    
    Set bla = Conn.execute(SQL)
    Response.Write bla("totaal")

En dan zou die 0 de Output in de sp voorstelllen.....

[Annie]

Je gooit een aantal zaken door elkaar. En dat zijn de return status van een sproc, een output parameter en een recordset.

Je kan een returnvalue uit je sproc krijgen alleen niet op de manier die jij nu doet. In de QA zou je dan doen:

SQL:

EXEC @retval = sproc 1, 2

Waarbij de sproc bijvoorbeeld iets is als:

SQL:

CREATE PROC sproc
  @a INT, @b INT
AS
  RETURN 1

Om een recordset terug te krijgen zal je (in jouw voorbeeld) moeten doen

ASP:

SQL = "Exec GetTotaalEndOfWeek " & Session("StringNR") & ", '" & ChangeDate(BeginDate) & "'"
    
Set bla = Conn.execute(SQL)
Response.Write bla("totaal")

Waarbij de sproc iets doet als

SQL:

CREATE PROC GetTotaalEndOfWeek
-- blabla
AS
  -- blabla
  SELECT @a + @b + @c AS totaal

En tot slot: bij het gebruik van een output parameter moet je ook aangeven dat dit een output var is, bijv (in de QA):

SQL:

DECLARE @kommaar INT

EXEC sproc 1, 2, @kommaar OUTPUT

SELECT @kommaar

Als je met output parameters gaat werken in ASP dan raad ik aan om gewoon te werken met een command object en de parameters collectie daarbij. Als je het met "EXEC ..." wil blijven doen dan zal je dus het recordset voorbeeldje moeten gebruiken.

[ Voor 5% gewijzigd door Annie op 16-04-2003 12:11 . Reden: ubb foutjes ]

[Jaspertje]

Goed, het is me gelukt, deels met jullie hulp, dank daarvoor.. de uitkomst:

SQL:

CREATE PROCEDURE totaal
    @User_id int,
    @Datum datetime

AS BEGIN
    DECLARE @StartKosten decimal
    DECLARE @Inkomsten decimal
    DECLARE @Uitgaven decimal
    DECLARE @totaal decimal

Select @StartKosten = (select (start_kas + start_bank) From tbl_start where user_id = @User_id)

Set @Inkomsten = (select SUM(inkomsten_bedrag) From tbl_inkomsten where user_id = @User_id AND Datum < @Datum)

if @Inkomsten = null begin
    set @Inkomsten = 0
end 

Select @Uitgaven = (select SUM(kosten) FROM tbl_uitgaven where user_id = @User_id AND Datum < @Datum)
if @Uitgaven = null Begin
    Set @Uitgaven = 0
end

Set @totaal = @StartKosten - @Uitgaven + @Inkomsten
select @totaal as totaal
END
GO