Get return value from stored procedure

[1DMF]

Hi,

I am using a querydef to execute a DAO.database stored procedure.

        [indent][/indent]    Dim db As DAO.Database
    Dim qd As QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs("spCC_UpdateRAG")
    qd.SQL = "EXEC spCC_UpdateRAG @Col = 'Rating', @RAG = " & Nz(vRate, "NULL") & ", @Case_ID = " & iCaseID
    qd.Execute

How do I get the return value?, the SP doesn't return a recordset it just returns a value, but I can't see how I get the value?

Thanks,
1DMF.



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

I have not done it, but assume you use the return parameter

http://msdn.microsoft.com/en-us/library/windows/desktop/ms675869(v=vs.85).aspx

 

[PHV]

MajP, the OP clearly stated that he uses DAO, not ADO.

1DMF, are you using an adp db or a passthru query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

Hi guys,

PHV -> Yes it's a pass-through query.

MajP -> I did try with return params, but it seems the protocol for a querydef object allows the setting of pramas but not return values. I also found that T-SQL requires you to provide values for output params, which is bizarre, and as they seem to be returned the same way a recordset is, I couldn't see their use other than to provide data typing.

I can't see much difference when using an OUTPUT parameter between...

SET @Result = 0
SELECT @Result AS Result

and

SELECT 0 AS Result

Other than the mentioned data typing.

I also don't really want a recordset object; what I would like to do is something like...

Dim bOK As Boolean
bOK = qd.Execute

Stack overflow are saying I need ADODB, and even claimed you need ADODB to utilise SQL Server, which I know is rubbish, because I use ONLY MS SQL Server 2008 R2 with DAO and have done for many, many years, as per the advice received here on TT when I moved over to late bindings when Office 2007 was released.

I have something working in my access VBA code...

' declare objects
Dim rs As DAO.Recordset
Dim qd As QueryDef

' set query def 
Set qd = CurrentDb.QueryDefs("myPassThroughQuery")

' set SP
qd.SQL = "EXEC myStoredProc @arg1 = 'val1', @arg2 = 'val2', @arg3 = val3"

' execute SP
Set rs = qd.OpenRecordet

' get result
bOK = rs.Fields("Result")

But I don't like it, it's a bit clunky and requires a recordset object, but if that's what I need to do then so be it, of course if you guys can come up with a nicer solution, I'm all ears





"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music