Solved

Returning Record Counts SQL Server Stored Procedure

Posted on 2011-02-18
2
366 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
2 Comments
 
LVL 142

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now