T-SQL: Using result of a dynamic SQL query in a variable or table

Although, not a recommended practice, but sometimes we have to write our queries using dynamic SQL. In such situations, it is generally needed to fetch the result (scalar or tabular) of dynamic SQL into the main (non-dynamic) query. This is not straight forward because dynamic SQL runs in its own scope and we cannot access the variables defined in main query. This post presents a few approaches to consume the result of a dynamic SQL query:

sp_ExecuteSql stored procedure

This is the most generic and powerful method of invoking dynamic SQL since it allows us to write a parameterized dynamic query with input/output parameters. Here’s a simple example of using sp_executesql to consume the result of a dynamic SQL query:


declare @today datetime
exec sp_executesql
    N'Select @internalVariable = GetDate()', --dynamic query
    N'@internalVariable DateTime output', --query parameters
    @internalVariable = @today output --parameter mapping
select @today

Table variables and Temporary tables

This method is used when we want to get a tabular result set from our dynamic query. Here’s an example to get the result of a dynamically created SQL query by using table variables:


declare @myTable table
(
    DatabaseName nvarchar(256),
    DatabaseID int,
    CreateDate datetime
)

insert into @myTable
    exec (N'select name, database_id, create_date from sys.databases') --dynamic query

select * from @myTable

Here’s the same example that uses a temporary table to fetch the result set of a dynamic SQL query:


create table #myTable
(
    DatabaseName nvarchar(256),
    DatabaseID int,
    CreateDate datetime
)

insert into #myTable
    exec (N'select name, database_id, create_date from sys.databases') --dynamic query

select * from #myTable
drop table #myTable

Since temporary tables have physical existence so we can refer to the temporary table inside our dynamic SQL query as well. Here’s an example illustrating this technique:


create table #myTable
(
    DatabaseName nvarchar(256),
    DatabaseID int,
    CreateDate datetime
)

--dynamic query
exec sp_executesql
    N'insert into #myTable
        select name, database_id, create_date from sys.databases'

select * from #myTable
drop table #myTable

Temporary tables or Table variables can also be used to fetch the result of a stored procedure. Notice that for saving this result, the columns of table variable/temporary table must match with the result of stored procedure. That is, we need to take “ALL” the columns. Here’s an example that grabs the result set from a stored procedure into a table variable.


declare @myTable table
(
    ServerName nvarchar(256),
    NetworkName nvarchar(256),
    Status nvarchar(4000),
    ID int,
    Collation nvarchar(256),
    ConnectTimeOut int,
    QueryTimeOut int
)

insert into @myTable
    exec sp_helpserver

select * from @myTable

Also, here’s an example to get result of a stored procedure using temporary table:


create table #myTable
(
    ServerName nvarchar(256),
    NetworkName nvarchar(256),
    Status nvarchar(4000),
    ID int,
    Collation nvarchar(256),
    ConnectTimeOut int,
    QueryTimeOut int
)

insert into #myTable
    exec sp_helpserver

select * from #myTable
drop table #myTable

Thats all from me. Let me know if you have any more solutions.

Advertisements

31 Responses to “T-SQL: Using result of a dynamic SQL query in a variable or table”

  1. DotNetShoutout Says:

    T-SQL: Using result of a dynamic SQL query in a variable or table « Mehroz’s Experiments…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. DotNetBurner - SQL Server Says:

    T-SQL: Using result of a dynamic SQL query in a variable or table « Mehroz’s Experiments…

    DotNetBurner – burning hot .net content…

  3. Ads Says:

    hmm… do you know of a way to insert the resultset from a dynamic piece of sql into a temporary table, not knowing how many columns are in that resultset?

  4. Syed Mehroz Alam Says:

    Hi Ads,

    You can use select into to insert the result to a temporary table, but you will not be able to consume that temporary table in the main query. Here’s an example demonstrating this:

    declare @dynamicSQL varchar(max)
    
    
    set @dynamicSQL = '
    --select query returning any no. of columns
    select getdate() as [Date], newid() as GUID, ''test'' as Text, 123 as Number
    into #temporaryTable 
    
    --select from temp table can be done inside the dynamic query 
    select * from #temporaryTable
    '
    
    --execute sql
    exec( @dynamicSQL )
    
    --can't use that temporary table outside
    select * from #temporaryTable --error: Invalid object name '#temporaryTable'
    
  5. ercan Says:

    I recommend everyone who has something to do with dynamic SQL to read this great article: http://www.sommarskog.se/dynamic_sql.html

  6. Vijay Says:

    Thanks you so much. I was just dying to see how I could mix @table with Dynamic Sql. This helped.

  7. click here Says:

    Hello there, You have done an incredible job. I will definitely
    digg it and personally recommend to my friends. I am sure they’ll be benefited from this website.

  8. benjamin plumbing supply tucson az Says:

    Write more, thats all I have to say. Literally, it seems as though you relied on the video to make your
    point. You definitely know what youre talking about, why waste your intelligence on just posting videos to
    your blog when you could be giving us something enlightening to read?

  9. http://www.picowiki.com/as-chicago-home-improvement-hints/index.php?action=all_name Says:

    Hello there! Do you use Twitter? I’d like to follow you if that would be ok. I’m
    absolutely enjoying your blog and look forward to new updates.

  10. ราคาตู้เย็น Says:

    Hey this is kind of of off topic but I was wondering if blogs use WYSIWYG editors or if you
    have to manually code with HTML. I’m starting a blog soon but have no coding know-how so I wanted to get advice from someone with experience. Any help would be greatly appreciated!

  11. Samual Says:

    Hi there would you mind stating which blog platform you’re working with? I’m looking to start my
    own blog soon but I’m having a hard time making a decision between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design seems different then most blogs and I’m looking
    for something unique. P.S Apologies for getting off-topic but I had to ask!

  12. orlando shuttle Says:

    Greetings! I know this is somewhat off topic but I was wondering if
    you knew where I could find a captcha plugin for my comment form?
    I’m using the same blog platform as yours and I’m having trouble finding one?
    Thanks a lot!

  13. Air Conditioner Says:

    Excellent goods from you, man. I’ve understand your stuff previous to and you are just extremely great. I actually like what you’ve acquired here,
    really like what you are stating and the way in which
    you say it. You make it enjoyable and you still take care of to keep it wise.
    I can’t wait to read far more from you. This is actually a great site.

  14. Jacki Says:

    It is not my first time to pay a quick visit this site, i am browsing this web
    page dailly and take nice facts from here daily.

  15. www.newswire.net Says:

    What’s up, every time i used to check website posts here early in the daylight, since i love to learn more and more.

  16. refrigerator parts and repair Says:

    I am curious which blogging and site-building platform you are using?
    I’m new to operating a blog and have been thinking about using the WordPress platform. Do you consider this is a good platform to start with? I would be really grateful if I could ask you some questions through e-mail so I can learn a bit more before getting started. When you have some free time, please make sure to contact me at: tracicovington@bigstring.com. Thanks alot 🙂

  17. http://miami8diy71.newsvine.com Says:

    When I originally commented I clicked the “Notify me when new comments are added” checkbox and
    now each time a comment is added I get three emails with the
    same comment. Is there any way you can remove me from that service?
    Cheers!

  18. http://en.netlog.com/patsyjzxm/blog Says:

    Hi there. I was contemplating adding a website link back to your site since both of
    our websites are based mostly around the same topic.
    Would you prefer I link to you using your website address:
    https://smehrozalam.wordpress.com/2009/10/14/t-sql-using-result-of-a-dynamic-sql-query-in-a-variable-or-table/ or web site title:
    T-SQL: Using result of a dynamic SQL query in a variable or table | Mehroz’s Experiments. Be sure to let me know at your earliest convenience. Thankyou

  19. lose a stone in a week Says:

    I absolutely love your blog and find the majority of your
    post’s to be what precisely I’m looking for. Do you offer guest writers to
    write content for you? I wouldn’t mind publishing a post or elaborating on a lot of the subjects you write concerning here. Again, awesome site!

  20. air conditioning in murrells inlet Says:

    I am genuinely thankful to the owner of this site who has shared this great article
    at at this place.

  21. build your own website Says:

    It’s an remarkable article in support of all the online visitors; they will take benefit from it I am sure.

  22. athletic tape and pre wrap Says:

    Its like you read my mind! You appear to know so much about this, like you wrote the book
    in it or something. I think that you could do with a
    few pics to drive the message home a bit, but other than that, this is excellent blog.
    A fantastic read. I will certainly be back.

  23. designs for small bathrooms Says:

    Small bathroom Xterra Remodel – Small bathtubs and showersA standard bathtub takes up a large portion of the renovation to the existing building.

    Tensions still ran high, and last year, have vanished.

  24. Athletic trainer jobs in florida Says:

    These are genuinely enormous ideas in concerning blogging.
    You have touched some good factors here. Any way keep up wrinting.

  25. soulcast.com Says:

    Hiya! My spouse and I often publish guest posts for other site
    owners to help increase publicity to our work, as well as provide
    excellent content to website owners. It really is a win
    win situation! If you happen to be interested feel free to contact me at: dustin_devries@gmail.com so we can
    communicate further. Appreciate it!

  26. http://craigwebbdreamtracker.com/the-particular-beginning-on-the-net-made-searching-for-the-fridge Says:

    I like what you guys are usually up too. This sort of clever work and reporting!
    Keep up the superb works guys I’ve included you guys to my personal blogroll.

  27. Stella Says:

    I love reading an article that will make men and women
    think. Also, many thanks for allowing me tto
    comment!

  28. Nestor Campos Says:

    Vientos eres como Hulk pero mas chingon gracias

  29. Nestor Campos Says:

    Great, you’re like Hulk but much better thanks

  30. seo hizmeti Says:

    Konu anlatımı mükemmel teşekkür ederim

  31. Nichole Says:

    We had a lot of trouble with the previous SEO company we hired,
    been researching it in my spare time and next time we will employ a junior to
    have a go


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: