How To Store Return Value In SQLServer 1

[gk4u]

Hi All

i want to make a function in sql-server 7.0 that takes
three parameters and return one parameter.
i am presently doing like this

CREATE PROCEDURE hello @p1 integer, @p2 integer, @p3 integer, @dt1 datetime OUTPUT as


DECLARE @DT AS DATETIME
set @dt = '1/1/00'

set @dt1 = dateadd(day,@p1,@dt)
set @dt1 = dateadd(month,@p2,@dt1)
set @dt1 = dateadd(year,@p3,@dt1)

set @dt1 = dateadd(day,-1,@dt1)
set @dt1 = dateadd(month,-1,@dt1)

return

now i want to collect the return value from procedure into visual
basic variable.i am using OLEDB connection.

help me.
Gaurav

 

[mwolf00]

Actually you want an ouput parameter - the RETURN in a sp can only return integers...

set objCmd = server.createobject("adodb.command&quot
Set objCmd.ActiveConnection = objConn
with objCmd
.commandText = "hello"
.CommandType = adCmdStoredProc
.parameters(1) = p1
.parameters(2) = p2
.parameters(3) = p3
.parameters(4).direction = adParamOutput
.parameters(4) = dt1
.Execute
End With

Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048

mikewolf@tst-us.com

 

[nealvenables]

I assume that it's @dt1 that you want to return?

The syntax on SQL is

CREATE PROC hello
@p1 integer,
@p2 integer,
@p3 integer,
@dt1 datetime OUTPUT
AS

SELECT @dt1 = dateadd(day,@p1,@dt)
SELECT @dt1 = dateadd(month,@p2,@dt1)
SELECT @dt1 = dateadd(year,@p3,@dt1)
SELECT @dt1 = dateadd(day,-1,@dt1)
SELECT @dt1 = dateadd(month,-1,@dt1)


In Access...

Dim adoCNN as New ADODB.Connection
Dim adoCMD as New ADODB.Command
Dim prm_p1 as ADODB.Parameter
Dim prm_p2 as ADODB.Parameter
Dim prm_P3 as ADODB.Parameter
Dim prm_dt1 as ADODB.Parameter

adoCNN.Open "YourSqlConnectionString"
adoCMD.ActiveConnection = adoCNN
adoCMD.CommandType = adCmdStoredProc
adoCMD.CommandText = "hello"

set prm_p1 = adoCMD.CreateParameter("", adInteger, adParamInput, , Your_p1_Variable)
set prm_p2 = adoCMD.CreateParameter("", adInteger, adParamInput, , Your_p2_Variable)
set prm_p3 = adoCMD.CreateParameter("", adInteger, adParamInput, , Your_p3_Variable)
set prm_dt1 = adoCMD.CreateParameter("", adDbDate, adParamOutput,,NULL)

adoCMD.Parameters.Append prm_p1
adoCMD.Parameters.Append prm_p2
adoCMD.Parameters.Append prm_p3
adoCMD.Paremeters.Append prm_dt1

adoCMD.Execute

MsgBox "The Stored Procedure Returned " & prm_dt1.Value



Does this help?

 

[coco86]

Hallelujah, my code now works!

I've been searching for hours to figure out why my SP output parameter was returning NULL.

I had this in my app

    With cmdStoredProc
        .CommandText = "testcopy"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@ID", 
                       adInteger, adParamInput, , OldID)
        .Parameters.Append .CreateParameter("@autonum", 
                       adInteger, adParamOutput, , NewID)
        .Execute
    End With
 
    CopyPO = NewID

but I changed it to match your example

    With cmdStoredProc
        .CommandText = "testcopy"
        .CommandType = adCmdStoredProc
        Set p1 = .CreateParameter("@ID", adInteger, 
                      adParamInput, , OldID)
        Set p2 = .CreateParameter("@autonum", adInteger, 
                      adParamOutput, , Null)
        .Parameters.Append p1
        .Parameters.Append p2
        .Execute
    End With
    
    copyPO = p2.Value

and I finally got the results. I still don't understand exactly why it didn't work the other way but at least it works now!

Thanks!

-Coco