Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
YvesT
Starting Member
1 Post |
 Posted - 2005-01-25 : 18:29:20
|
| Hello,I want to return exact 10 rows from a recordset, even if the select staement returns only 5 records, the rest of the records should be filled with null values.This can be done with dynamic SQL within a stored proc using a UNION SELECT TOP (10-@ActiualRowsReturned) after the initial SELECT statement.But you can't execute this dynamic SQL in a UDF, and that's a pitty because I wanted to return this 'outcome' as a table for using it in (another) stored proc which JOINS this recordset with another table.Some1 know how this can be done ?Thanks,Yves. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-25 : 19:25:26
|
| Don't use a UDF, use a stored procedure. Or, create a temp table, insert 10 blank rows, then INSERT the real rows. From there you can SELECT TOP 10 from the temp table, using an ORDER BY clause so that the real rows always appear first.Why exactly do you need 10 rows, especially if they're just empty? |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-01-25 : 19:28:56
|
| Rob, he wants to join on the resultset, why not use a UDF for this?That leads to your question though, if you are joining on the remainder null values you won't get anything. YvesT, what is the point of having exactly 10 rows?-ec |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-25 : 20:32:09
|
| Well, for the fact that temp tables and dynamic SQL are not allowed in a UDF. You can use a temp table anyway by doing INSERT INTO #temp EXEC myStoredProc, and then JOIN to the temp table. Seems like a good method to me, I don't see any particular advantage a UDF might give even without the restrictions it imposes. Without knowing more about the requirements though, it's hard to say what's best here. |
|
|
|
|
|
|
|
|
exact 10 rows