Solved

TSQL Return Record Counts with Dataset

Posted on 2010-09-20
10
500 Views
Last Modified: 2012-05-10
Hi Experts,

I've got a stored proc that returns search results using FREETEXTTABLE and all works fine.

However, I would love to also return the count of records that have a value < £100, £100 - £300, £300 - £600, £600 - £1000 and > £1000.

I've got a count select statement to work within the SP, I just dont know how to pass these extra results though a function and onto the web page.

Any help would be greatly appreciated even if it's to tell me this is a bad idea!

Regs,
Numb
0
Comment
Question by:ComfortablyNumb
  • 5
  • 3
  • 2
10 Comments
 
LVL 1

Expert Comment

by:mmhorton65
ID: 33717122
What are you doing to get the data into the webpage?
0
 

Author Comment

by:ComfortablyNumb
ID: 33717255
Im calling a function in classic ASP. All pretty simple stuff I'm affraid:


Function GetSearch(varSearchTerm, varSID, varSearchSort)

	dim rstMX: Set rstMX = Server.CreateObject("ADODB.Recordset")

	rstMX.Open "sp_getSearch '" & varSearchTerm & "'," & CLng("0" & varSID) & "," & CLng("0" & varSearchSort), oConn , adOpenStatic
	
	If Not rstMX.EOF then
		GetSearch = rstMX.GetRows()
	End If
	
	Set rstMX = Nothing

End Function

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33717436
under bottom of your sp add this

select
when val < 100 then '< 100'
when val between 100 and 300 then '100-300'
when val between 300 and 600 then '300-600'
...
when val > 1000 then '> 1000'
end as grp, count(1) grp_count
from FREETEXTTABLE
group by case
when val < 100 then '< 100'
when val between 100 and 300 then '100-300'
when val between 300 and 600 then '300-600'
...
when val > 1000 then '> 1000'
end

so, when you run your sp, you will get 2 dataset, one is previous resul, the second one will be this
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:ComfortablyNumb
ID: 33717521
I've actually got a select statement set up in the SP already and returns a count but how do I actually retrieve the counts via the function?
0
 
LVL 1

Expert Comment

by:mmhorton65
ID: 33717831
Retrieve the counts ASP wise?
0
 

Author Comment

by:ComfortablyNumb
ID: 33723810
Yes via the function
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33727729
in sp add the select that i posted before...
when you run the sp, you will get two dataset...

if you are using datareader use NextResult to get the second resultset that has the counts...
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.nextresult.aspx
0
 

Author Comment

by:ComfortablyNumb
ID: 33734143
OK thats cool BUT how do I get the two seperate datasets pulled in via the Function as one dataset is a datalist using GetRows and the other would normally be passed as a variable with an array holding the counts.

Its this bit Im really stuck on...
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 33736945
dont use sf, use sp
in the sp you will have

create procedure x(... input params ...) as
begin
  select ...
  select ...
end;
0
 

Author Closing Comment

by:ComfortablyNumb
ID: 34022143
Thanks for the help. got me there in the end
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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 a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

777 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