Solved

TSQL Return Record Counts with Dataset

Posted on 2010-09-20
10
516 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:Huseyin KAHRAMAN
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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:Huseyin KAHRAMAN
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:
Huseyin KAHRAMAN 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 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