Stored Proc. OUTPUT param Returning NULL in VBA

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Proc. OUTPUT param Returning NULL in VBA

Author

Topic

loch006
Starting Member

1 Post

Posted - 2005-11-04 : 10:10:19

I am writing an excel 2003 macro in VBA that accesses a SQL Server 2000 (sp3) database.

The macro needs to get a return value from a stored procedure but the OUTPUT param i have in the stored proc is returning NULL by the time it reaches the VBA ADO ADODB.Command object I have setup to receive it.

When I have the stored procedure print out the value of that OUTPUT param before the procedure exits it has the correct value, but when it reaches the ADODB.Command object it says null.. I am not sure why but here is the Stored procedure code:

CREATE PROCEDURE dbo.spGetRecruitingStats

@strViewName varchar(255),
@strDateCompare varchar(255),
@intPositionId int,
@intNumCands int = 0 OUTPUT

AS

SET NOCOUNT ON

/*declare a variable to hold our dynamic SQL command string*/
DECLARE @strSQL varchar(1000)

/*create temporary table for passing variables between dynamic and real-time SQL batches*/
CREATE TABLE #tempNumCands (
NumCands int
)

/*set the SQL command string*/
SET @strSQL = 'INSERT INTO #tempNumCands' +
' SELECT SUM(NumCandidates) AS NumCands' +
' FROM ' + @strViewName +
' WHERE ' + @strDateCompare + ' AND PositionId = ' + CONVERT(varchar,@intPositionId) + ';'

/*execute the SQL command*/
Execute(@strSQL)

SET @intNumCands = (SELECT * FROM #tempNumCands)
SELECT @intNumCands AS NumCands

/*RETURN @intNumCands*/

PRINT @intNumCands
PRINT @strSQL

DROP TABLE #tempNumCands
GO

Here is the VBA code which executes and should get the correct value back from the stored proc:

Function GetStatsFromDBView(ByVal strViewName As String, ByVal strWHERE_Clause, ByVal intPositionid As Integer) As Integer
Dim cmd As ADODB.Command
Dim parm As ADODB.Parameter
Dim intNumCands As Integer

Set cmd = New ADODB.Command
cmd.ActiveConnection = objADOConn
cmd.CommandText = "spGetRecruitingStats"
cmd.CommandType = adCmdStoredProc
'cmd.Parameters.Refresh

Set parm = cmd.CreateParameter("@strViewName", adVarChar, adParamInput, 255, strViewName)
cmd.Parameters.Append parm
Set parm = cmd.CreateParameter("@strDateCompare", adVarChar, adParamInput, 255, strWHERE_Clause)
cmd.Parameters.Append parm
Set parm = cmd.CreateParameter("@intPositionId", adInteger, adParamInput, 4, intPositionid)
cmd.Parameters.Append parm
Set parm = cmd.CreateParameter("@intNumCands", adInteger, adParamOutput, 4)
cmd.Parameters.Append parm

cmd.Execute Options:=adExecuteNoRecords

'store the return value from the stored procedure
intNumCands = cmd.Parameters("@intNumCands").Value

GetStatsFromDBView = intNumCands

End Function

cmd.Parameters("@intNumCands").Value is always returning NULL for some reason. Does it have to do with the temporary table i am using to pass values between a dynamic SQL batch and a normal SQL batch?? Any and all help is greatly appreciated.

-Loch

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-04 : 10:27:38

I would suggest tracing the sql that is sent to the database server using sql profiler. Take the captured code and execute it manually in a QA window. That will check if the code you're sending is correct as well as if the sql code that was sent executes correctly. If both those tests pass, then you know there is something in your vba code after the call to the database.

the capture sql should look something like this:


declare @xxxx int
exec dbo.spGetRecruitingStats @strViewName = 'myView',@strDateCompare '1/1/2005'
    ,@intPositionId = 1,@intNumCands = @xxxx OUTPUT

--add this line to check value
select @xxxx

Be One with the Optimizer
TG

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-11-07 : 01:00:51

i think you want to get the count?

quote:

SET @intNumCands = (SELECT * FROM #tempNumCands)

select @intNumCands=count(*) from #tempNumCands

HTH

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources