[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2008-01-28
2
Medium Priority
?
691 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:zimmer9
  • 2
2 Comments
 
LVL 6

Accepted Solution

by:
grendel777 earned 2000 total points
ID: 20763828
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

0
 
LVL 6

Expert Comment

by:grendel777
ID: 20763853
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question