Output Parameters for a newbie
-
I am trying to write my first stored procedures.
Spent hours on the net ... bought like three FAT books ...
Really frustrating.
When I put OUTPUT on my last two parameters and try to test run it says I did not supply values for INPUT parameters.
What gives? All I want to do is get some info back to Crystal Reports. Elsewhere I have read recently that output parameters can only be used to return to a calling SP_
-- K. Mike Bradley
--It took me hours and hours of waisted time on the net to find out that ...
--T SQL debugger needs datetime in this format including curly braces:
--{ ts '2003-01-01 00:00:00'}
CREATE PROCEDURE dbo.sp_TrendMax
-- declare the input parameters
@TID int,
@StartDate datetime,
@EndDate datetime,
-- declare the output parameters
@final_peak decimal(19,4) OUTPUT,
@final_date datetime OUTPUT
AS
DECLARE @evaluate VARCHAR(50)
DECLARE @evaluate_date datetime
DECLARE @current decimal(19,4)
DECLARE @current_date datetime
DECLARE @Temp decimal(19,4)
-
btw.. Sql server doesn't need to have '{' around the dates.. this is usually to have some compatibility with MS Access.
Here's an exemple of how to execute a stored proc that returns a return parameter, output parameter and a recordset from query analyser :
create proc dbo.demo @param1 as int, @paramOUT as int output
as
set nocount on
set @ParamOUT = 2 * @Param1 + 1
Select top 1 name, XType from dbo.SysObjects
return 10
set nocount off
go
Declare @Return as int
Declare @Out as int
set @Out = 1000
exec @Return = dbo.demo 9, @out output
Select @return as returnvalue, @Out as outputparam
drop proc demo
-
I ran that and get the same error:Server: Msg 201, Level 16, State 4, Procedure demo, Line 0
Procedure 'demo' expects parameter '@paramOUT', which was not supplied.
It's almost as if the keywork "OUTPUT" is ignored and the parameter is treated as an input.
-
Where do you run this code from?
-
SQL Querry Anylizer ...
execute demo 1
-
Look at this carefully, you must supply the output parameter to the proc...
Declare @Return as int
Declare @Out as int
set @Out = 1000
exec @Return = dbo.demo 9, @out output
Select @return as returnvalue, @Out as outputparam
The @Return = is just for the return value of the proc.. maybe you don't need it at this point but I thaught you migh tlike to learn how to do it.
-
BTW this is how to call the proc from VB6 :
MyCn is a global connection to the application
Private Function exec_demo(ByVal param1 As Integer, ByRef paramOUT As Integer, Optional ByRef ReturnValue As Integer) As ADODB.Recordset
On Error GoTo Gestion
Dim MyCmd As ADODB.Command
Set MyCmd = New ADODB.Command
MyCmd.CommandText = "dbo.demo"
MyCmd.CommandType = adCmdStoredProc
Dim MyParam As ADODB.Parameter
Set MyParam = New ADODB.Parameter
MyParam.Direction = adParamReturnValue
MyParam.Name = "@Return"
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@param1"
MyParam.Value = param1
MyParam.Size = 4
MyParam.Direction = adParamInput
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@paramOUT"
MyParam.Value = paramOUT
MyParam.Size = 4
MyParam.Direction = adParamInputOutput
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Dim MyRs As ADODB.Recordset
Set MyRs = New ADODB.Recordset
MyRs.CursorLocation = adUseClient
MyCn.Open
MyCmd.ActiveConnection = MyCn
MyRs.Open MyCmd, , adOpenKeyset, adLockOptimistic
If MyRs.State = 1 Then
Set exec_demo = MyRs.Clone
exec_demo.ActiveConnection = Nothing
Else
Set exec_demo = Nothing
End If
MyCn.Close
ReturnValue = CInt(MyCmd.Parameters("@Return").Value)
paramOUT = MyCmd.Parameters("@paramOUT").Value
DisposeRS MyRs
Set MyParam = Nothing
Set MyCmd = Nothing
Exit Function
Gestion:
ErrHandler ModuleName, Me.Name, "exec_demo", Err
MsgBox Err.Description & " : " & Err.Number
End Function
-
You mean that even though it is an output parameter you need to put something in it when called ??????
I am totaly confused.
All I want to do is return the row with a maximum so I though I had to do it with parameters for the two columns I care about.
here is what I got:
-- K. Mike Bradley
--It took me hours and hours of waisted time on the net to find out that ...
--T SQL debugger needs datetime in this format including curly braces:
--{ ts '2003-01-01 00:00:00'}
CREATE PROCEDURE dbo.sp_TrendMax
-- declare the input parameters
@TID int,
@StartDate datetime,
@EndDate datetime,
-- declare the output parameters
@final_peak decimal(19,4) OUTPUT,
@final_date datetime OUTPUT
AS
DECLARE @evaluate VARCHAR(50)
DECLARE @evaluate_date datetime
DECLARE @current decimal(19,4)
DECLARE @current_date datetime
DECLARE @Temp decimal(19,4)
-- declare the cursor
DECLARE trend_cursor CURSOR Static Read_Only
FOR
SELECT DATE_STAMP_ "Date_Time", DATA_VALUE_ "Value"
FROM TRENDDATA
WHERE TID_ = @TID AND @StartDate <= DATE_STAMP_ AND @EndDate >= DATE_STAMP_
OPEN trend_cursor
SET @current = -10000
-- Start Loop here
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- fetch the next DATA_VALUE_
FETCH NEXT FROM trend_cursor INTO @evaluate_date, @evaluate
-- convert from char to an integer
SET @Temp = CONVERT(decimal(19,4), @evaluate )
-- compare @Temp to @current. Is it higher?
IF @Temp > @current
-- If so, store in current peak's
BEGIN
SET @current = @Temp
SET @current_date = @evaluate_date
END
-- Last row? If not, loop back to start.
END
-- Store current to final.
SET @final_peak = @current
SET @final_date = @current_date
-- clean up
CLOSE trend_cursor
DEALLOCATE trend_cursor
RETURN 0
GO
-
You don't need to put anything in it, but you must declare it. I set it to something to show you that its value was changed in the proc.
Also I think that this will do the same thing without using a cursor :
SELECT top 1 DATE_STAMP_ "Date_Time", max(DATA_VALUE_) "Value"
FROM TRENDDATA
WHERE TID_ = @TID AND @StartDate = DATE_STAMP_
group by DATE_STAMP_
order by max(DATA_VALUE_) desc
-
I tried for days to get group by to work ......
Pardon me while I pull what's left of my hair out.
DO you want to make some cash money?
DO you have a paypal account?
Please help me ..
-
That almost works except DATA_VALUE_ is a VARCHAR(50)
SO I need to CONVERT to decimal.
-
BTW, a simple thanx will do.
-
This will fail if the data is not numeric, you might want to change the column's datatype to decimal if a varchar is not its most meaningfull datatype.
SELECT top 1 DATE_STAMP_ "Date_Time", max(CAST(DATA_VALUE_ as Decimal(18,4))) "Value"
FROM TRENDDATA
WHERE TID_ = @TID AND @StartDate = DATE_STAMP_
group by DATE_STAMP_
order by max(CAST(DATA_VALUE_ as Decimal(18,4))) desc
-
Thanks indeed but I have a lot of work past this I need to get done.
btw the database is given to me I can't change it. It is from our APP that we sell for automation systems.
I need to create several SP_'s to return data to Crystal Reports for our customer. They have a guy who does Crystal and he spent months tring to do all this client side.
The first SP_ was easy and just returns a range of trend data for a particular point from a start date to an end date.
The second needs to return the max and you just got that for me (as well as the min I think).
The third needs to use cursors to examin each record from start date to end date and return the sum of all the peaks. If the next value is less than the previous add the previous to a temp variable.
I am more than happy to whip out the company credit card to get it done rather than spend three months trying to learn.
-
I'm flattered but I don't have the time to take on a contract at the moment...
Maybe you could do a post in the jobs posting of this site. There are many more competent dbas/programmers that hang out here... even much better than I am (A few MVPs post here from time to time).
As for the avg peaks, I'd like to try to make it for you cause I always love a new challenge
. Can you post the table definition, some sample data along with the expected output you want?Also on a side note, if you are using reporting services, or access to create the reports, you can use the min, max()... functions directly on the report, meaning you don't even need to write those procs yourself
.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply