Nesting a stored proc call in a where clause?
-
Here's the short question - how can I make this work? create function callme() returns TABLE AS RETURN(exec MyExistingSP) And the longer question on why I'd want it to... * I've got several stored procs that return table result sets that are fairly large (or larger than I care to scroll through) * Often, I'm doing ad-hoc queries where I only want to see one or two rows returned by the stored proc. * If I need to whip up a function/stored proc to allow this, it would be handy if it would take the stored proc name as a parameter * Would also be nice to take a subset of the column list in a select, or add an order by * I'm most interested in SQL Server 2000+ * Performance is not an issue (within some reason...), as this is only for typed in ad-hoc queries select column1, column2 from callme('MySP') where id > 500 order by LastModified --> would be nice to see the subset... I can do this easily with a multiline function for each stored proc, but it's not worth that much work. I keep hoping there's just a twist of syntax I'm missing, but I'm afraid that's not the case... Any thoughts? Thanks, Dave
-
Here's the short question - how can I make this work? create function callme() returns TABLE AS RETURN(exec MyExistingSP) And the longer question on why I'd want it to... * I've got several stored procs that return table result sets that are fairly large (or larger than I care to scroll through) * Often, I'm doing ad-hoc queries where I only want to see one or two rows returned by the stored proc. * If I need to whip up a function/stored proc to allow this, it would be handy if it would take the stored proc name as a parameter * Would also be nice to take a subset of the column list in a select, or add an order by * I'm most interested in SQL Server 2000+ * Performance is not an issue (within some reason...), as this is only for typed in ad-hoc queries select column1, column2 from callme('MySP') where id > 500 order by LastModified --> would be nice to see the subset... I can do this easily with a multiline function for each stored proc, but it's not worth that much work. I keep hoping there's just a twist of syntax I'm missing, but I'm afraid that's not the case... Any thoughts? Thanks, Dave
-
Here's the short question - how can I make this work? create function callme() returns TABLE AS RETURN(exec MyExistingSP) And the longer question on why I'd want it to... * I've got several stored procs that return table result sets that are fairly large (or larger than I care to scroll through) * Often, I'm doing ad-hoc queries where I only want to see one or two rows returned by the stored proc. * If I need to whip up a function/stored proc to allow this, it would be handy if it would take the stored proc name as a parameter * Would also be nice to take a subset of the column list in a select, or add an order by * I'm most interested in SQL Server 2000+ * Performance is not an issue (within some reason...), as this is only for typed in ad-hoc queries select column1, column2 from callme('MySP') where id > 500 order by LastModified --> would be nice to see the subset... I can do this easily with a multiline function for each stored proc, but it's not worth that much work. I keep hoping there's just a twist of syntax I'm missing, but I'm afraid that's not the case... Any thoughts? Thanks, Dave
Dave2909 wrote:
create function callme() returns TABLE AS RETURN(exec MyExistingSP)
You can't do this - 1. You can't call a proc from a function 2. You can't pipe the output from a proc into a table Regarding only getting the first few rows, try
SET ROWCOUNT 10
exec sproc
SET ROWCOUNT 0This will just return the 1st 10 rows. As for the rest of it, the only way to do it would be some pretty complex dynamic sql.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
-
Dave2909 wrote:
create function callme() returns TABLE AS RETURN(exec MyExistingSP)
You can't do this - 1. You can't call a proc from a function 2. You can't pipe the output from a proc into a table Regarding only getting the first few rows, try
SET ROWCOUNT 10
exec sproc
SET ROWCOUNT 0This will just return the 1st 10 rows. As for the rest of it, the only way to do it would be some pretty complex dynamic sql.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
Thanks, Bob...just wish there was a different answer. The more I think about this though, it just seems that there really should be some way to do a sub-query against a stored proc's result set from SQL Server management studio without having to define specific tables, or do anything funky like parsing the SP to auto-create a result table. Perhaps I just need to add this into a feature request for MS. Just in case...anyone else have a suggestion on how to narrow down the results of a stored proc from within Management studio? Something like being able to run a query against the results window sure would be handy. Thanks, Dave