Solved

TSQL Return Record Counts with Dataset

Posted on 2010-09-20
10
519 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
[X]
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
  • 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 55

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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 55

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 55

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

696 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