Passing a temp table to a stored procedure

With SQL 2008, table variables can now be passed into stored procedures.  Temp tables can also be passed into stored procedures from other stored procedures and triggers.  This is true in 2008 and earlier versions like SQL 2005 and 2000.   This allows for cleaner code as many lined stored procedures can now be broken up into multiple sub-procedures. 

A child stored procedure can see its parent's temp table. This child procedure can INSERT, DELETE and UPDATE rows just like a normal temp table.  When the child returns to the parent, the data is changed and can be passed into more child stored procedures.

Using a temp table and passing it to another procedure, in most cases, will be faster than looping through a result set with a cursor and calling a stored procedure with parameters. 

An example:

CREATE PROC dbo.udfParent
AS
BEGIN

    SELECT 1 AS MyInt,
    'Hello' AS MyChar
    INTO #myTemp

    EXEC dbo.udfChild

    SELECT *
    FROM #myTemp

    DROP TABLE #myTemp

    RETURN (0)
END

GO

CREATE PROC dbo.udfChild
AS
BEGIN

    SELECT *
    FROM #myTemp

    UPDATE #myTemp
    SET MyInt = 2

    RETURN (0)
END

GO

EXEC dbo.udfParent

 

What did you think of this article?




Trackbacks
  • No trackbacks exist for this entry.
Comments
Page: 1 of 4
  • 8/14/2009 google adwords secrets wrote:
    Awesome information in here. Thank you so much.
    Reply to this
    1. 4/16/2010 SEO Company California wrote:
      I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post.
      Reply to this
      1. 4/22/2010 Milestone Search wrote:
        I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.
        Reply to this
        1. 6/12/2010 Bubble letters wrote:
          I'm going to blog about that...
          Reply to this
          1. 7/30/2010 2 Day Diet Japan Lingzhi wrote:
            2 Day Diet Japan Lingzhi 2 Day Diet Japan Lingzhi Super Slim Pomegranate Super Slim Pomegranate Pai You GuoPai You Guo Botanical Slimming Botanical Slimming Super Slim Super SlimGreen Lean BodyGreen Lean BodyLida Daidaihua Lida Daidaihua Fruit and Plant Weight Loss capsule Fruit and Plant Weight Loss capsule 3X Slimming Power 3X Slimming Power Instant SlimInstant Slim Zhen De Shou Zhen De Shou P57 Hoodia P57 Hoodia Fat Loss Slimming Beauty Fat Loss Slimming Beauty
            Reply to this
    2. 4/22/2010 IT Infrastructure management wrote:
      I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post.
      Reply to this
      1. 7/14/2010 Security Training wrote:
        Thanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with more information? It is extremely helpful for me.
        Reply to this
        1. 8/18/2010 Raman wrote:
          Best Software Solutions Software Attack experts in rapid custom development of softwares, web-based, distributed and standalone applications designed to meet your organization's specific requirements and business needs.
          Reply to this
      2. 7/26/2010 Material Testing wrote:
        This is a really good post. Must admit that you are amongst the best bloggers I have read. Thanks for posting this informative article.
        Reply to this
        1. 7/26/2010 Cakephp Website Development wrote:
          Just read your post and would like to thank you for maintaining such a cool blog.
          Reply to this
    3. 4/26/2010 Email Marketing Software wrote:
      Nicely presented information in this post, I prefer to read this kind of stuff. The quality of content is fine and the conclusion is good. Thanks for the post.
      Reply to this
      1. 6/26/2010 Online Marketing wrote:
        the way of presented and the step are well and clearly updated,this was very easy to understand thank you..
        Reply to this
    4. 7/22/2010 Application Hosting wrote:
      This was a useful post and I think it is rather easy to see from the other comments as well that this post is well written and useful. Keep up the good work.
      Reply to this
    5. 7/23/2010 Los Angeles Web Designer wrote:
      Sql 2008 is cool at it allows the Temp tables to pass into stored procedures from other stored procedures and triggers.
      Reply to this
    6. 7/23/2010 Hawaii Web Design wrote:
      Will this new SQL table will unable the child stored procedure to have a look at its parent's temp table?
      Reply to this
    7. 7/23/2010 Website Development wrote:
      A child procedure in SQL table 2008 will be able to INSERT, DELETE and UPDATE rows just like a normal temp table.
      Reply to this
    8. 7/23/2010 Free Websites wrote:
      This new table allows the several lined stored procedures to break up into multiple sub-procedures, which result into cleaner codes.
      Reply to this
    9. 8/26/2010 backhoe wrote:
      Ya really thanks for the information.
      Reply to this
  • 8/28/2009 Cheap Auto Insurance wrote:
    ahhh.. been looking for this everywhere.. thanks
    Reply to this
  • 11/14/2009 jeu du poker wrote:
    Really i am impressed from this post....the person who create this post he is a great human..thanks for shared this with us.i found this informative and interesting blog so i think so its very useful and knowledge able.I would like to thank you for the efforts you have made in writing this article
    Reply to this
  • 12/9/2009 cheap banners wrote:
    Which indian based companies spend maximum money for advertisements in India?
    Reply to this
    1. 4/23/2010 web design PA wrote:
      There are many companies in India spend maximum money for advertisement..... you can search in internet and you can find a lot....
      Reply to this
  • 12/16/2009 Bulk SMS wrote:
    HI!
    thanks for sharing such good informations with us.
    regards to everyone.
    Reply to this
    1. 2/26/2010 buy laptop wrote:
      Great work buddy, keep it up
      Reply to this
  • 12/19/2009 club penguin cheats wrote:
    Greet the authors of the site. Want to express thanks for good work.Your article very useful and interesting.
    Reply to this
  • 12/25/2009 Cheap Web Hostin wrote:
    I am impressed from this post....the person who create this post he is a great human..thanks for shared this with us.i found this informative and interesting blog so i think so its very useful and knowledge able.I would like to thank you for the efforts you have made in writing this article
    Reply to this
    1. 7/27/2010 data wrote:
      I agree. thanks a lot for posting this not article, but piece of art, for us to wonder how you achieved it.
      Reply to this
  • 12/28/2009 Bulk sms wrote:
    hey! can someone teach me some coding, i am very weak in my coding ways.
    Reply to this
    1. 5/9/2010 seo company wrote:
      You should be capable of doing the coding by yourself...the coding requires lot of technical work...
      Reply to this
  • 1/18/2010 Download new Movies wrote:
    the person who create this post he is a great human..thanks for shared this with us.
    Reply to this
    1. 5/26/2010 ecommerce company wrote:
      Really man this website provide good help to everyperson...
      Reply to this
  • 1/19/2010 awol again yacht wrote:
    Thanks for the information.
    Reply to this
    1. 6/28/2010 IT Service Management wrote:
      Nicely presented information in this post, I prefer to read this kind of stuff. The quality of content is fine and the conclusion is good. Thanks for the post.
      IT Service Management
      Reply to this
  • 2/2/2010 seo expert India wrote:
    Before you call the pest control company know what problem you have, how you want it handled, and how the pest control technician inspects and treats for bugs, mice, and rats. Knowing the inspection process up front brings you valuable service for eliminating the insects from your home.
    Reply to this
  • 2/2/2010 monitor stands wrote:
    Excellent blog post, I look forward to reading more.
    Reply to this
    1. 3/18/2010 Application Hosting wrote:
      Thanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with more information? It is extremely helpful for me.
      Reply to this
    2. 3/18/2010 Cs Hacks wrote:
      I found your website perfect for my needs. It contains wonderful and helpful posts. I have read most of them and got a lot from them. To me, you are doing the great work. Carry on this. work at home In the end, I would like to thank you for making such a nice website.
      Reply to this
    3. 3/18/2010 Printer Ink wrote:
      Useful information shared..Iam very happy to read this article..thanks for giving us nice info.Fantastic walk-through. I appreciate this post.
      Reply to this
    4. 3/18/2010 Miami Computer IT Support wrote:
      It's very informative posting, actualy i'm new in the domain matter, so this writing help me much increase my knowledge.
      Reply to this
    5. 3/18/2010 Datacenter Services wrote:
      Good post....thanks for sharing.. very useful for me i will bookmark this for my future needed.thanks for a great source.
      Reply to this
  • 2/3/2010 event organisers melbourne wrote:
    awesome thanks for the information
    Reply to this
  • 2/3/2010 brisbane airport car rental wrote:
    Don’t stop blogging! It’s nice to read a sane commentary for once
    Reply to this
    1. 8/2/2010 Port transportation miami wrote:
      just now i started to learn SQL,it is very interesting,while connecting to database it is too fun to do...
      Reply to this
  • 2/3/2010 sms service wrote:
    Great post, you’ve helped me a lot
    Reply to this
  • 2/3/2010 camera shops sydney wrote:
    Don’t stop blogging! It’s nice to read a sane commentary for once
    Reply to this
    1. 3/18/2010 Kodak Camera wrote:
      This is my first time i visit here. I found so many interesting stuff in your blog especially its discussion. From the tons of comments on your articles, I guess I am not the only one having all the enjoyment here! keep up the good work.
      Reply to this
    2. 4/26/2010 Kodak Impresoras wrote:
      I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post.
      Reply to this
  • 2/4/2010 Ottawa Fleet Detailing wrote:
    The information served as a missing link in my business. Please keep writing.
    Reply to this
  • 2/4/2010 business directory wrote:
    Nice job, it’s a great post. The info is good to know!
    Reply to this
  • 2/5/2010 oxygen sensor wrote:
    I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.
    Reply to this
    1. 3/15/2010 search engine optimisation UK wrote:
      I salute the authors of the site. I express gratitude for the good article work.Your very useful and interesting.
      Reply to this
  • 2/7/2010 Cheap Bulk SMS wrote:
    The query has helped me to dump me to write a stored procedure for dumping into temp table and than using the temp table to query with join and actual table used for dumping and getting the results.Thanks for sharing.
    Reply to this
  • 2/9/2010 Harry Dcousta wrote:
    Hi,
    Thanks for the information and links you shared that should be a useful and quite informative!



    HarryDcousta,
    SEO Pakistan
    SEO Services Pakistan
    Reply to this
  • 2/9/2010 Harry Dcousta wrote:
    Hi,
    Great post you have shared and i 'm totally impressed by the post!
    Thanks for sharing.......



    HarryDcousta,
    SEO Pakistan
    SEO Services Pakistan
    Reply to this
  • 2/10/2010 Life insurance wrote:
    Great post, I look forward to reading more.
    Reply to this
  • 2/10/2010 SEO wrote:
    Cheers for the info. It was a good read.
    Reply to this
    1. 4/26/2010 dofollow blog link list wrote:
      Great post - keep up the good work.
      Reply to this
  • 2/11/2010 SEO Guru Pakistan wrote:
    Excellent efforts to emphasize these points,Thanks for sharing....
    Reply to this
  • 2/11/2010 India property reviews wrote:
    Some of the best advice comes from this website. From a new blogger/website prospective and my knowledge of SEO this approach is probably the best. For the more experiences webmasters posting on nofollow websites could be more beneficial. My guess is we probably need both, but the technique in the article should work well for start me out my site. Lord knows, I still have tons to learn.
    Reply to this
  • 2/13/2010 promozione del casino online wrote:
    TABLE type parameters are not supported Mapping CLR Parameter Data. You have other options though, eg. pass state as a BLOB (SqlBytes) or as XML (SqlXml).
    Reply to this
    1. 7/29/2010 Sloggi wrote:
      sloggi invites YOU to a charity beach party celebrating fashion, art, music and love all under one roof
      Reply to this
  • 2/16/2010 logo designs wrote:
    Well thanks for the tip keep sharing!
    Reply to this
  • 2/18/2010 image library wrote:
    thanks for these awesome information
    Reply to this
  • 2/18/2010 betfair wrote:
    thanks for this wonderful post, Keep it up
    Reply to this
    1. 2/22/2010 promotional flash drives wrote:
      Nice blog. Keep up the good work
      Reply to this

Page: 1 of 4
Leave a comment

 Enter the above security code (required)

 Name

 Email (will not be published)

Your comment is 0 characters limited to 3000 characters.