Returing a recordset | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Returing a recordset

Hi, I’m new to T-SQL (my experience is with PL/SQL) and I’m a little confused about writing a procedure/function to return a recordset. I’m not sure if I should use a procedure with no OUT parameters which simply returns the result of a SELECT, or return a temporary table as an OUT parameter. The recordset I need to return does not correspond to any particular table, but instead each row will contain a set of statistics calculated from various tables. For example let’s say it contains a date, and mean & stddev values. If anyone can suggest a good approach and even maybe some skeleton T-SQL I’d be really grateful. Also, most of these statistics can I think be calculated either using a stored procedure OR through use of views. Is there any particular benefit to either way – I don’t think the views would be used except internally. Thanks.
Hi,
Your approach should be to create a stored procedure and put all your T-SQL code to return the complete recordset which will be needed at front end.
After that from front end execute a call to above SP and access the recordset returned.
Loop through the recordset and display the data on screen.
Avoid firing any ad-hoc queries from your front end tool.
You should be following just one way i.e. access your server only through SPs.
Thankyou for your help, but it’s the "return a recordset" step I’m asking about. Since my recordset isn’t just rows from a table, and can’t really be built through a query. I think i should use a table datatype in my procedure, and then my procedure should end with SELECT * FROM my_temporary_table?
In cases where you want to return just a single row with one column value like max(column), then
you can store the result in a variable and declare it as output variable which will be much quicker to access in front end for eg: from ADO command object.

I definitely have multiple rows. The value for each column is actually calculated in a different way. What I pass into the procedure is a time period, each row returned contains various statistics about one particular week.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by jdx</i><br /><br />I definitely have multiple rows. The value for each column is actually calculated in a different way. What I pass into the procedure is a time period, each row returned contains various statistics about one particular week.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />So in this scenario, you are better off with returning one complete recordset to avoid round robin calls to server.<br />But i’m still confused with your statement that "I think i should use a table datatype in my procedure, and then my procedure should end with SELECT * FROM my_temporary_table?"<br /><br />What do you mean by this. Are you doing row by row processing i.e. one date at a time from whole week. If yes then you may end up with performance hit end of the day.<br />You can post some sample data and your desired output resultset, and we may help you to achieve the desired result in one select, instead of storing one row at a time in table variable.[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]
I can’t really give more detail on the tables and data… but let me try to explain better 1)I have objects whose data is split across many tables
2)Each object has a timestamp
3)Each row that I return has data concerning every object whose timestamp is in a specific week.
4)In a row, each column contains different calculations on all the objects that week – these calculations are too many and too complicated to make a single query. A bit like: say I have a database about people. I want to group all the people born in each week, and for that group I want to calculate average height, % of people that own a car, etc.
So I have records returned like:
week_start_date average_height percent_own_car
1/1/2007 176 56.7
8/1/2007 187 54.3
.
.
.
But in my case, each statistic is quite complicated to do. And obviously the columns in my returned recordset do not match any real table column. So don’t I have to create a temporary table with colums week_start_date, average_height, percent_own_car, do lots of queries to populate this table, and then return SELECT * on this table? I hope I explained this well enough? The procedure is not simply doing a query but retrieving lots of data and returning it as one convenient object. Performance is not the biggest aim here, but soemthing that a DAO app can call and simply get returned a recordset.
]]>