EXECUTE

  • Lokesh Vij

    SSChampion

    Points: 10836

    Comments posted to this topic are about the item EXECUTE

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • kapil_kk

    SSC-Insane

    Points: 21316

    At first line of query its written as Use Master, so all statement stating SELECT db_name() gives master, execute statement will not make any effect here.....

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • This was removed by the editor as SPAM

  • Dineshbabu

    Hall of Fame

    Points: 3220

    I thought EXECUTE statement will change the DB.

    Learning for me.

    --
    Dineshbabu
    Desire to learn new things..

  • ralm

    Hall of Fame

    Points: 3156

    It will make effect Kapil. Try the below...

    As in the explanation the scope is only within the EXECUTE statement.

    USE master;

    -- Query#1

    SELECT DB_NAME() DatabaseName1;

    EXECUTE ('USE AdventureWorks2008; SELECT DB_NAME() DatabaseName2');

    -- Query#2

    SELECT DB_NAME() DatabaseName2;

    EXECUTE ('USE AdventureWorks2008; select count(*) from Sales.Customers; USE master; ');

    -- Query#3

    SELECT DB_NAME() DatabaseName3;

    [font="Verdana"]Regards,
    Rals
    [/font].
  • Ken Wymore

    SSCoach

    Points: 16611

    Nice question Lokesh. I never would have thought to try changing the database through and execute statement. Now I know what would happen had I tried!

  • kapil_kk

    SSC-Insane

    Points: 21316

    THANKS RALS,

    the query that you posted will make effect through EXECUTE but the EXECUTE statement that is post in the today's QOD is not making any effect it will always print 'Master' database

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Stuart Davies

    SSCoach

    Points: 18878

    Thank for the question - easy way to end the week

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

  • Toreador

    SSChampion

    Points: 11257

    How long until the first complaint that the correct answer is None of the Above, because there's no such database as AdventureWorks2008 😉

  • Mighty

    SSCrazy Eights

    Points: 8811

    Toreador (2/15/2013)


    How long until the first complete that the correct answer is None of the Above, because there's no such database as AdventureWorks2008 😉

    That was until your reply. 😀

    But in fact it is the case if you don't have that database. It would have been better if tempdb was used, since that one is available on all servers.

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Thanks for the question, Simply Nice 🙂

    (we have like 250+ database on each principals where the structure of each database is same, only the data stored is different, hosted on multiple database server over different regions. I once wrote a query to fetch a specific set of records from one server on all databases and I used this "execute use db1" and the execute the underlying sql statement and then store them in the table variable.. the whole query was awful and then I changed in to one whole batch and then stored it in to the temp table and used the cursor to loop trough the next database and then execute it and then store in result... the complete execution took only 15 seconds to fetch records from all database..like some less than 600 records... I actually came to now here that executing of the "use" statement using "execute" does not changes the physical connection of the current session. memory recall 🙂 )

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • TomThomson

    SSC Guru

    Points: 104773

    Toreador (2/15/2013)


    How long until the first complaint that the correct answer is None of the Above, because there's no such database as AdventureWorks2008 😉

    Surely no one will be quite that silly?

    I just assumed that the idea was to assume it was run against SQL Server 2008 with that DB installed, since it's clear from the question and the answer options that one has to assume the database exists and that seems most likely to happen with an SQLS 2008 installation.

    Of course the database name would have to change to AdventureWorks2008R2 for those statements to work on my laptop, but who cares - it's not as if I would actually want to run that code anyway!

    Tom

  • Venkat Jonnalagadda

    Mr or Mrs. 500

    Points: 510

    an easy one to start my day...and its a friday!!!!!!!!!! 😀

    ___________________________________________________________________
    If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:

  • EL Jerry

    SSCertifiable

    Points: 7053

    Great question to end the week. Thank you, Lokesh.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • Lynn Pettis

    SSC Guru

    Points: 442339

    I think this is not quite correct:

    Database context will not be changed while executing EXECUTE statements. The reason is changes in database context last only until the end of the EXECUTE statement.

    Really should be more like:

    The database context will only be changed within the context of the EXECUTE statement. It will have no effect on the context in which the EXECUTE statement is run.

    Other than that, very good back to basics question. Surprised by the number of people that have missed the question so far.

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply