We help IT Professionals succeed at work.

How to use output parameters in SQL Server using Access VBA ?

I am developing an Access application using Access as the front end and SQL Server as the back end database. I use the following statements to call a Stored Procedure:

DoCmd.Hourglass True
    Set com = New ADODB.Command
    With com
       .CommandTimeout = 95
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procUDLLNMod"
       '.Parameters.Append .CreateParameter("RptYear", adInteger, adParamInput, 4, intYearSP)
       .ActiveConnection = CurrentProject.Connection
       Set rstQueryFS = .Execute
    End With

The commented out line above shows an example of how I would use an Input parameter when calling the stored procedure.

How would I call this stored procedure using an output parameter named @REC_NUMBER ?
What statement would I use to execute the stored procedure using SQL Sever Enterprise ?

I know the following doesn't work:

DECLARE @REC_NUMBER int
exec dbo.procUDLLNMod @REC_NUMBER
-------------------------------------------------------
(56152 row(s) affected)

Server: Msg 208, Level 16, State 1, Procedure procUDLLNMod, Line 17
Invalid object name 'dbo.tblUDLLN'.
----------------------------------------------------------------------------------------------------------------------
The following is the Stored Procedure which would store the record count in the output parameter
@REC_NUMBER .

CREATE PROCEDURE dbo.procUDLLNMod
@REC_NUMBER INT OUTPUT
AS
If  Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblUDLLN' AND TYPE = 'U')
DROP TABLE tblUDLLN
SELECT DISTINCT
CASE WHEN C.TaxIDInd = '1' THEN ' ' ELSE UPPER(C.FirstName) END AS [1st First Name],
UPPER (C.MiddleInitial) AS [1st Mid],              
CASE WHEN C.TaxIDInd = '1' THEN C.FirstName ELSE UPPER(C.LastName) END AS [1st Last Name],
UPPER(C.SecondNameFirst) As [2nd First Name], UPPER(C.SecondNameMid) AS [2nd Mid], UPPER(C.SecondNameLast) AS [2nd Last Name],
UPPER(C.StreetAddr1) AS [Address 1], UPPER (C.StreetAddr2) AS [Address 2],
UPPER(C.City) AS City,
UPPER(C.ResStateCode) AS [State], C.Zip AS Zip, CASE WHEN LEN(C.SSN) = 0 THEN C.TaxID ELSE C.SSN END AS [SSN/Tax ID],  
Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number], C.DateOfBirth AS [Date Of Birth], C.AcctExec AS [FA Number]
INTO tblUDLLN
FROM dbo.tblCustomersNew As C  INNER JOIN dbo.tblProductsNew As P ON C.CustomerNumber = P.CustomerNumber AND C.OfficeNumber = P.OfficeNumber

SELECT @REC_NUMBER = COUNT(*) FROM dbo.tblUDLLN

GO
Comment
Watch Question

Hi zimer9, I think you're close enough to feel it - I think the code below accomplishes what you need.  You just need to declare the output parameter in the command object.  It also shows how to get the output param for use in your code.

HTH!
With com
    .CommandTimeout = 95
    .CommandType = adCmdStoredProc
    .CommandText = "dbo.procUDLLNMod"
  'Input Parameters:
    .Parameters.Append .CreateParameter("RptYear", adInteger, adParamInput, 4, intYearSP)
  'Output Parameters:
    .Parameters.Append .CreateParameter("@REC_NUMBER", adInteger, adParamOutput)
    .ActiveConnection = CurrentProject.Connection
End With
 
Set rstQueryFS = com.Execute
Dim intRecNo As Integer
intRecNo = com.Parameters("@REC_NUMBER").Value

Open in new window

Oops, one more thing - you might need to process the entire recordset before you can access the output parameter's value because it is returned at the end of the RS.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.