SP Not Returning Value

[booboo0912]

I'm testing my SP to see if my values are being returned...but I'm getting blank/nothing/zilch/nada. So I decided to test if setting the param equal to a string, then returning it in my asp code would work...seems like it should, but it doesn't!

Here's my SP (modified):

Create Procedure addItem
@Name varchar OUTPUT
As
Set Nocount On
Set @Name = 'hi there'
Return @Name

My code for the parameter is:
cmd.Parameters.Append cmd.CreateParameter("@Name",adVarChar,adParamOutput,50)

Then to output value:
dim test
test = cmd.Parameters("@Name&quot.Value
Response.Write test

Why isn't "hi there" being returned?
Thanks!!!!!

 

[chriscorran]

Maybe it's because you've simplified your code but theres no mention of you calling the actual procedure. Ie. You have a stored procedure that returns a value, but you dont actually say to the procedure, do your thing.

Heres a simple stored procedure and some code that uses it. Hope this helps. PS. This example returns a value but not a recordset.

The stored procedure takes in a parameter (valueIN) and returns a value (valueOUT)

The code snippet takes in an integer value and returns a value.

CREATE PROCEDURE sp_getAValue

@valueIN int,
@valueOUT varchar(50) OUTPUT

AS

SELECT @valueOUT = name
FROM yourTable
WHERE id = @valueIN


In your code

With cmdObj
.ActiveConnection = conn
.CommandText = "sp_getAValue"
.CommandType = adCmdStoredProc

' Quotes data
.Parameters.Append .CreateParameter("@valueIN", adInteger, adParamInput, 4, varSomeInputVariable)

' Create output parameter
.Parameters.Append .CreateParameter("@valueOUT", adVarchar, adParamOutput, , 0)

.Execute , , adExecuteNoRecords

varYourReturnedValue = .Parameters("@valueOUT&quot

End With

 

[SteveRuso]

This seems to work:

CREATE Procedure addItem
@Name nvarchar(50) OUTPUT
As
Set Nocount On
Set @Name = 'hi there'
return

and the vb code:

Dim cmd As New Command

With cmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "addItem"
.Parameters.Append .CreateParameter("@Name", adVarWChar, adParamOutput, 50)
.Execute
MsgBox (.Parameters(0))
End With


--The code was written in VBA so would need to be adapted of course (just for an example):

Dim cmd As New Command

With cmd
.ActiveConnection = conn
.CommandType = adCmdStoredProc
.CommandText = "addItem"
.Parameters.Append .CreateParameter("@Name", adVarWChar, adParamOutput, 50)
.Execute
Response.Write Parameters(0)
End With

Hope this helps...

 

[cmmrfrds]

Here is a function that has an example of sending and viewing parameters. If you use a return parm it needs to be the 1st parm. The function shows a couple of ways of reading the returned parms.


Function CommandObjectSP()
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter, param4 As Parameter

Dim var2 As String, recAffected As Integer
Dim fld As Field, er As Error

Set cnn = CurrentProject.Connection

Set cmd.ActiveConnection = cnn
Set param1 = cmd.CreateParameter("ReturnVal", adSmallInt, adParamReturnValue)
cmd.Parameters.Append param1

Set param2 = cmd.CreateParameter("InInt", adSmallInt, adParamInput)
cmd.Parameters.Append param2
param2.Value = 3
Set param3 = cmd.CreateParameter("Outparm", adVarChar, adParamOutput, 20)
cmd.Parameters.Append param3
param3.Value = "XXXXXX"

' Set up a command object for the stored procedure.
cmd.CommandText = "dbo.sp_supplier"
cmd.CommandType = adCmdStoredProc

Set rst = cmd.Execute

For Each er In cnn.Errors
Debug.Print "error is: "; er.Number; " with value = "; er.description
Next

For Each fld In rst.Fields
Debug.Print "field is: "; fld.name; " with value = "; fld.Value
'-- Debug.Print "field name = "; fld.Value
Next

Debug.Print "return code = "; cmd.Parameters("ReturnVal&quot.Value
Debug.Print "input value = "; cmd.Parameters("InInt&quot.Value
Debug.Print "output value = "; cmd.Parameters("OutParm&quot.Value
Debug.Print "return code = "; cmd(0)
Debug.Print "input value = "; cmd(1)
Debug.Print "output value = "; cmd(2)

End Function

 

[booboo0912]

WOW...thanks for all of your replies! I should've checked this sooner! A couple things were discovered...I put SELEC NULL instead of Return @Name, and it returned 'h'...then I gave the varchar a length (I had @Name varchar OUTPUT), and it returned the whole string!
Thanks for all of your help!