Search
Monday, November 12, 2018 ..:: Home ::.. Register  Login
   Calendar  
     
  
   Search  
     
  
   Blogroll  
     
  
   Disclosure  
All blog entries are the opinions of the author and do not necessarily reflect the opinions of their employer. All the code presented is for explanation and demonstration purposes only. Any damages incurred to your site and/or data are not the responsibility of the author. Every effort is taken to ensure the code properly compiles, however sometimes there are some hiccups and you might be required to do your own debugging.
     
  
   TechTidBits (Blog)  

Excecuting a stored proc and doing something with the returned table data

Apr 7

Written by:
Tuesday, April 07, 2009 8:58 PM  RssIcon

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(
  PKTABLE_QUALIFIER sysname,  
  PKTABLE_OWNER sysname,  
  PKTABLE_NAME sysname,  
  PKCOLUMN_NAME sysname,  
  FKTABLE_QUALIFIER sysname,
  FKTABLE_OWNER sysname,
  FKTABLE_NAME  sysname,
  FKCOLUMN_NAME sysname,
  KEY_SEQ smallint,
  UPDATE_RULE smallint,    
  DELETE_RULE   smallint,
  FK_NAME       sysname,  
  PK_NAME       sysname, 
  DEFERRABILITY smallint
)
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(
  PKTABLE_QUALIFIER sysname,  
  PKTABLE_OWNER sysname,  
  PKTABLE_NAME sysname,  
  PKCOLUMN_NAME sysname,  
  FKTABLE_QUALIFIER sysname,
  FKTABLE_OWNER sysname,
  FKTABLE_NAME  sysname,
  FKCOLUMN_NAME sysname,
  KEY_SEQ smallint,
  UPDATE_RULE smallint,    
  DELETE_RULE   smallint,
  FK_NAME       sysname,  
  PK_NAME       sysname, 
  DEFERRABILITY smallint
)

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! :>

 

Resources:

TechTidBits: Are all tables created equally?, Peter Henry

MSDN: System Stored Procedures (Transact-SQL)

Tags:
Categories:
Location: Blogs Parent Separator TechTidBits

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel 
     
  
Copyright 1999-2012 by PCHenry.com   Terms Of Use  Privacy Statement