Post reply

Return specific columns from a sp

  • March 14, 2006 at 1:58 pm

    #112171

    I am trying to return only a selected few columns from a stored procedure.

    As an example the stored procedure sp_who returns the following columns:spid,ecid,status,loginame,hostname,blk,dbname,cmd

    Lets say I need only the spid,loginname,hostname,dbname

    If I use select spid,ecid,status,loginame,hostname,blk,dbname,cmd

    from (exec sp_who) this does not work.

    I know I could put the results into a temp table and then select only the columns I need but for my needs that will not solve the problem.

    Does anyone have a solution?

    Thanks

     

    Gary

  • March 14, 2006 at 2:09 pm

    #626639

    insert into #Who ()...

    exec sp_who2 

    SELECT .. from #Who

    _____________
    Code for TallyGenerator

  • March 14, 2006 at 2:29 pm

    #626646

    Can you explain why inserting in a temp table does not works for you?

     


    * Noel

  • March 14, 2006 at 3:50 pm

    #626664

    "Can you explain why inserting in a temp table does not works for you? "

    I am trying to find ways of speeding up a stored procedure.

    Inside the stored procedure I call another stored procedure and I am trying to limit the number of rows returned. I am already providing filtering criteria to the called sp but I want to reduce the result set even futher.

     

    So returning the entire result set of sp_who,for example, into a temp table does not offer any speed improvment.

    Thank for your reply.

    Gary

     

  • March 14, 2006 at 4:51 pm

    #626674

    If this is a botleneck in your SP performance you are lucky!

    You must have fastest SPs in the world!

    Look in the execution plan of your SP. You must have find out some thick lines after Index Scan with futher Hash Join. That means it creates hash table in tempdb with huge number of rows behind the scene.

    And if you don't see it it does not mean it does not slow down your query.

    _____________
    Code for TallyGenerator

  • March 15, 2006 at 8:23 am

    #626781

    copy the sp_who2 proc into another and  modify that one to return only what you want.

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • March 28, 2006 at 3:08 pm

    #629306

    take the stored procedure and make it a function. Functions can be used in the FROM part of a query...

Viewing 7 posts - 1 through 6 (of 6 total)

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