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
Solved

TSQL Return Record Counts with Dataset

Posted on 2010-09-20
10
507 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

789 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