SQL Server Performance Forum – Threads Archive
Returing a recordsetHi, 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.
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.