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
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
Awesome information in here. Thank you so much.
Reply to this
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
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
I'm going to blog about that...
Reply to this
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
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
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
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
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
Just read your post and would like to thank you for maintaining such a cool blog.
Reply to this
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
the way of presented and the step are well and clearly updated,this was very easy to understand thank you..
Reply to this
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
Sql 2008 is cool at it allows the Temp tables to pass into stored procedures from other stored procedures and triggers.
Reply to this
Will this new SQL table will unable the child stored procedure to have a look at its parent's temp table?
Reply to this
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
This new table allows the several lined stored procedures to break up into multiple sub-procedures, which result into cleaner codes.
Reply to this
Ya really thanks for the information.
Reply to this
ahhh.. been looking for this everywhere.. thanks
Reply to this
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
Which indian based companies spend maximum money for advertisements in India?
Reply to this
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
HI!
thanks for sharing such good informations with us.
regards to everyone.
Reply to this
Great work buddy, keep it up
Reply to this
Greet the authors of the site. Want to express thanks for good work.Your article very useful and interesting.
Reply to this
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
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
hey! can someone teach me some coding, i am very weak in my coding ways.
Reply to this
You should be capable of doing the coding by yourself...the coding requires lot of technical work...
Reply to this
the person who create this post he is a great human..thanks for shared this with us.
Reply to this
Really man this website provide good help to everyperson...
Reply to this
Thanks for the information.
Reply to this
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
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
Excellent blog post, I look forward to reading more.
Reply to this
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
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
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
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
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
awesome thanks for the information
Reply to this
Don’t stop blogging! It’s nice to read a sane commentary for once
Reply to this
just now i started to learn SQL,it is very interesting,while connecting to database it is too fun to do...
Reply to this
Great post, you’ve helped me a lot
Reply to this
Don’t stop blogging! It’s nice to read a sane commentary for once
Reply to this
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
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
The information served as a missing link in my business. Please keep writing.
Reply to this
Nice job, it’s a great post. The info is good to know!
Reply to this
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
I salute the authors of the site. I express gratitude for the good article work.Your very useful and interesting.
Reply to this
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
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
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
Great post, I look forward to reading more.
Reply to this
Cheers for the info. It was a good read.
Reply to this
Great post - keep up the good work.
Reply to this
Excellent efforts to emphasize these points,Thanks for sharing....
Reply to this
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
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
sloggi invites YOU to a charity beach party celebrating fashion, art, music and love all under one roof
Reply to this
Well thanks for the tip keep sharing!
Reply to this
thanks for these awesome information
Reply to this
thanks for this wonderful post, Keep it up
Reply to this
Nice blog. Keep up the good work
Reply to this