Excecuting a stored proc and doing something with the returned table data
Tuesday, April 07, 2009 8:58 PM
Stored procs are great, especially the ones from SQL Server, but the problem is using them in another query. How do you do it? Continue reading for insight! :>
Lately I've run into a problem, SQL Server has a stored proc I want to use called sp_pkeys to return the primary keys on a given table. My problem isn't that I don't know how to call sp_pkeys, that's easy. No, my problem is I want to do something with the table of data that sp_pkeys returns.
I found myself searching ALL over the place for an easy way to convince SQL Server to do all the grunt work for me. But nothing! :< Turns out, the solution is for the person (that's you and me :>) who wants to use the table coming back from the proc, to declare another table EXACTLY like the one being returned, insert values into that one and THEN and only THEN can you continue with your own logic. Just seems bit weird to me that I just can't declare a "table" and let SQL Server create the structure for me, ya for free!
Update 04-08-2009: The issue isn't about getting the primary keys, this can be done with other methods (like system views). No, the issue is using the table of data returned back from a stored proc in another query. That's the heart of this issue/question. I would ultimately like to do something like this SELECT * FROM MyProcName('parm1Value'). See how simple that is? THAT'S the sql syntax I would LOVE to see.
hhhhmmmmm I can sense a few DBAs either killing themselves laughing or cringing at "another developer looking to make their lives easier but who don't really know the underbelly of the beast." DOH! HAHA Hey, I know enough about SQL Server to respect it, and you, the DBA too (I know a few DBA who are GODS, GJ, DM, SM, CH quickly come to mind). But when it comes to getting work done, having to declare a twenty five column table structure to simple pull back data about ONE column AND get the datatyps correct, isn't my idea of a fun time!
Ok, enough time expressing my dislike with having to do the grunt work myself. Let's get on with it. sp_pkeys returns a 14 column table and can be declared in two ways. If you remember, I have already blogged about the different types of temp tables, I'll show how to use two, one local temp table (#TempTable) and another temp variable (@TempTable). The main difference between them is the #TempTable requires you to drop the table if you're going to be using the same command window to test it over and over again, and the @TempTable is created in memory only and supported in SQL Server 2000 and higher. If you're temp table requires a lot of rows, you might want to consider using the #TempTable (local scope) instead of the @TempTable (table variable).
Here's an example of creating and using a local scoped temp table.
create table #pkTableNames(
insert #pkTableNames exec sp_fkeys @fktable_name = 'Orders'
select PKTABLE_NAME from #pkTableNames order by PKTABLE_NAME ASC
drop table #pkTableNames
Here's an example of creating and using a temp table variable.
declare @PKTableNames as table(
insert @PKTableNames exec sp_fkeys @fktable_name = 'Orders'
select PKTABLE_NAME from @PKTableNames order by PKTABLE_NAME ASC
I hope you can see both ways are very similar. The key to getting the data into your newly created temp table is the insert statement which is pretty straight forward (now that you see it in action that is :>). The only other major difference is the dropping of the local #TempTable. This is not comletely necessary if you're doing this once in your stored proc, however, if you're like me and re-run your sql over and over in the same window while doing "iterative develoment," then proper housekeeping and dropping the table is required practise.
Now that you know how to use table data coming back from a stored proc, it's time to go grab a coffee and get coding! :>
TechTidBits: Are all tables created equally?, Peter Henry
MSDN: System Stored Procedures (Transact-SQL)