?
Solved

Returning Record Counts SQL Server Stored Procedure

Posted on 2011-02-18
2
Medium Priority
?
387 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore 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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

764 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