Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Returning Record Counts SQL Server Stored Procedure

Posted on 2011-02-18
2
Medium Priority
?
388 Views
Last Modified: 2012-05-11
SQL Rookie here...

I am trying to return a Count from a SQL stored Procedure.  When I run it returns the value that is assigned in the .Parameters("@ReportCount") = x statement.  When I enter the passed parameters directly into the SQL it pulls back a count.  I believe that it works, but not passing the parameters.  In the .Parameters.Append .CreateParameter("@ReportCount", 2, 3, 5) statement, I have tried both 2 and 3 as the next to last parameter for Output and Input/Output.  2 returns a null value.  3 does not change that value from what I passed in.

USE [ORS]
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectTransactionRange]    Script Date: 02/18/2011 08:14:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_SelectTransactionRange]
   @FromDate  varchar(10),
   @ToDate varchar(10),
   @JOBNAME varchar(10),
   @JOBNAME2 varchar(10),
   @USERDATA varchar(10),
   @SPLFNAME varchar(10),
   @Frequency varchar(1),
   @ReportCount int Output
AS

SELECT @ReportCount = COUNT(*)
FROM dbo.Transactions
WHERE JOBNAME=@JOBNAME and JOBNAME2=@JOBNAME2 and USERDATA=@USERDATA and SPLFNAME=@SPLFNAME and Frequency=@Frequency and [Transaction Date]>= @FromDate and [Transaction Date] <= @ToDate
GROUP BY JOBNAME, JOBNAME2, USERDATA, SPLFNAME, Frequency

Open in new window


	Set cmd = CreateObject("ADODB.Command")
	Set cmd.ActiveConnection = conTransactions

	with cmd
		.CommandText = "dbo.sp_SelectTransactionRange"
		.CommandType = adCmdStoredProc
		.Parameters.Append .CreateParameter("@FromDate", adVarChar, 1, 10)
		.Parameters("@FromDate") = "01/01/2011"
		.Parameters.Append .CreateParameter("@ToDate", adVarChar, 1, 10)
		.Parameters("@ToDate") = "02/15/2011"
		.Parameters.Append .CreateParameter("@JOBNAME", adVarChar, 1, 10)
		.Parameters("@JOBNAME") = rsGetAS400("JOBNAME2")
		.Parameters.Append .CreateParameter("@JOBNAME2", adVarChar, 1, 10)
		.Parameters("@JOBNAME2") = rsGetAS400("JOBNAME2")
		.Parameters.Append .CreateParameter("@USERDATA", adVarChar, 1, 10)
		.Parameters("@USERDATA") = rsGetAS400("USERDATA")
		.Parameters.Append .CreateParameter("@SPLFNAME", adVarChar, 1, 10)
		.Parameters("@SPLFNAME") = rsGetAS400("USERDATA")
		.Parameters.Append .CreateParameter("@Frequency", adVarChar, 1, 1)
		.Parameters("@Frequency") = rsGetAS400("FTPFLAG")
		.Parameters.Append .CreateParameter("@ReportCount", 2, 2, 1)
		.Parameters("@ReportCount") = 0
	
		.Execute
		
		intReportCount = .Parameters("@ReportCount")
	end with

Open in new window

0
Comment
Question by:michd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 34925470
please change:
.Parameters("@ReportCount") = 0


into:
.Parameters("@ReportCount").Direction = 2 'adParamOutput

to be able to fetch the result later
0
 

Author Closing Comment

by:michd
ID: 34925928
Thank you...
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

650 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