Output parameter vs Input/Output parameter

Output parameter vs Input/Output parameter

Post by Tek Bo » Sun, 26 Mar 2000 04:00:00



Howdy... I'm an ASP developer who is just starting to delve deeper into SQL
(SQL Server 7).  I'm writing up a wrapper function in ASP using ADO for an
SS7 stored procedure..... apparently ADO makes the distinction between an

************************************

    CREATE PROCEDURE bPackageExists (

    )
    AS  .....

************************************

Does anybody know how to create a 'pure' output parameter -- one that can't
possibly take an inital value?  Thanks in advance....

-=Tek Boy=-

 
 
 

Output parameter vs Input/Output parameter

Post by Darren Gree » Mon, 27 Mar 2000 04:00:00



writes

>Howdy... I'm an ASP developer who is just starting to delve deeper into SQL
>(SQL Server 7).  I'm writing up a wrapper function in ASP using ADO for an
>SS7 stored procedure..... apparently ADO makes the distinction between an


>************************************

>    CREATE PROCEDURE bPackageExists (

>    )
>    AS  .....

>************************************

>Does anybody know how to create a 'pure' output parameter -- one that can't
>possibly take an inital value?  Thanks in advance....

>-=Tek Boy=-

Q.      How can I get a return value or output parameter from a stored
procedure in VB/VBScript?

A.      You must use the ADO Command object and create parameters for
the procedure. The following sample is written for ASP (VBScript) and
writes the parameter values to the page, but can easily be used in VB -

i) Sample Stored Procedure Code



  RETURN Year(GetDate())

ii) Sample ASP VBScript Code

    Const adInteger = 3
    Const adChar = 129
    Const adParamOutput = 2
    Const adParamReturnValue = 4
    Const adCmdStoredProc = 4

    Dim sServer, sDatabase, sConnection
    sServer = "."
    sDatabase = "master"
    sConnection = "Provider=SQLOLEDB;Data Source=" & sServer & ";Initial
Catalog=" & sDatabase & ";Integrated Security=SSPI;"

    Dim oConn, oCmd
    Set oConn = CreateObject("ADODB.Connection")
    oConn.Open sConnection

    Set oCmd = CreateObject("ADODB.Command")
    oCmd.ActiveConnection = oConn
    oCmd.CommandText = "spGetDate"
    oCmd.CommandType = adCmdStoredProc

    oCmd.Parameters.Append oCmd.CreateParameter(, adInteger,
adParamReturnValue)
    oCmd.Parameters.Append oCmd.CreateParameter("DateOut", adChar,
adParamOutput, 10, "")

    oCmd.Execute

    Response.Write "<p>" & oCmd.Parameters(0).Value
    Response.Write "<p>" & oCmd.Parameters(1).Value

    Set oCmd = Nothing
    oConn.Close
    Set oConn = Nothing
--
Darren Green
SQL/DTS - http://www.swynk.com/friends/green/

 
 
 

Output parameter vs Input/Output parameter

Post by msnews.microsoft.co » Tue, 28 Mar 2000 04:00:00


Quote:> Does anybody know how to create a 'pure' output parameter -- one that
can't
> possibly take an inital value?  Thanks in advance....

Are you asking if it is possible to have SQL ignore values passed to an
output parameter?  Sure.  Simply set the value of the parameter to Null in
the first couple of lines of the stored procedure and then have your code
set it afterwards.  Thus, even if someone writes some nefarious code that
passes a value, that value will be ignored.

Tom