Solved

Returning Record Counts SQL Server Stored Procedure

Posted on 2011-02-18
2
384 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 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

705 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