Getting return value from a stored procedure SQL Server

[Anis]

Hello !
I want to execute a Stored procedure, sp, on sql Server. this sp will return an integer value and will take 3 parameters.

Is there a way to get the return value without populating parameters.
idealy the solution can be like this.
x = cn.execte("Execute spProblem 1, 2, 3")

Thanks in advance.
Anis

[Manish]

hi
which database object u r using to coonect to the sql server. U cann't use execute statement for output parameters or return value.
if u r using RDO or ADO use query obj or command object respectively. I amnt sure abt DAO but might be there shld be some object similer to query.use that.
Thanx Manish

[Anis]

I am trying to use ADO in an ASP Page.
Thanks,
Anis

[parehmulye]

Hi All....

I am calling a stored procedure using VB6 and SQL 2k
The problem is the stored procedure does not always return a recordset.But when i check the call through Query analyser the set of records is displayed.....

Pls help...this is a code used

dbcmd.CommandText = "sp_r65_addresscheck"
dbcmd.CommandType = adCmdStoredProc
dbcmd.ActiveConnection = dbconnR65

city_with_zip.Type = adVarChar
city_with_zip.Size = 255
city_with_zip.Direction = adParamInput

street_with_number.Type = adVarChar
street_with_number.Size = 255
street_with_number.Direction = adParamInput

country.Type = adVarChar
country.Size = 3
country.Direction = adParamInput


language.Type = adVarChar
language.Size = 1
language.Direction = adParamInput


zipcode.Type = adVarChar
zipcode.Size = 255
zipcode.Direction = adParamInput


city.Type = adVarChar
city.Size = 255
city.Direction = adParamInput


streetname.Type = adVarChar
streetname.Size = 255
streetname.Direction = adParamInput


house_number.Type = adVarChar
house_number.Size = 15
house_number.Direction = adParamInput



box_number.Type = adVarChar
box_number.Size = 15
box_number.Direction = adParamInput


dbcmd.Parameters.Append country
dbcmd.Parameters.Append city_with_zip
dbcmd.Parameters.Append language
dbcmd.Parameters.Append zipcode
dbcmd.Parameters.Append city
dbcmd.Parameters.Append street_with_number
dbcmd.Parameters.Append streetname
dbcmd.Parameters.Append house_number
dbcmd.Parameters.Append box_number

Set rsTMPICC = Open_Recordset("select CPAYSCOD,null,CLANGUE,CPOST,TLOCAL,null,TADRESS,PHOUSNR,PBOXNRB from TMP_ICC")
If rsTMPICC.RecordCount > 0 Then

rsTMPICC.MoveFirst
Do While Not rsTMPICC.EOF
Set rs = Nothing

country.Value = (rsTMPICC(0)) 'Mid((rsTMPICC(0)), 1, 1)
city_with_zip.Value = (rsTMPICC(1))
language.Value = (rsTMPICC(2))
zipcode.Value = (rsTMPICC(3))
city.Value = (rsTMPICC(4))
street_with_number.Value = (rsTMPICC(5))
streetname.Value = (rsTMPICC(6))
house_number.Value = (rsTMPICC(7))
box_number.Value = (rsTMPICC(8))

Set rs = dbcmd.Execute()


For i = 0 To rs.Fields().Count - 1
MsgBox rs(i).Name & "----->" & IIf(IsNull(rs(i)), "", rs(i))
Next

' Set dbcmd = Nothing
rsTMPICC.MoveNext
'rs.Close
Loop
End If