Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

TSQL Return Record Counts with Dataset

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
ComfortablyNumb
Asked:
ComfortablyNumb
  • 5
  • 3
  • 2
1 Solution
 
mmhorton65Commented:
What are you doing to get the data into the webpage?
0
 
ComfortablyNumbAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ComfortablyNumbAuthor Commented:
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
 
mmhorton65Commented:
Retrieve the counts ASP wise?
0
 
ComfortablyNumbAuthor Commented:
Yes via the function
0
 
HainKurtSr. System AnalystCommented:
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
 
ComfortablyNumbAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
dont use sf, use sp
in the sp you will have

create procedure x(... input params ...) as
begin
  select ...
  select ...
end;
0
 
ComfortablyNumbAuthor Commented:
Thanks for the help. got me there in the end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now