SQL Server Performance

Retrive Column From Stored Procedure

Discussion in 'Getting Started' started by lovely02525, Apr 19, 2007.

  1. lovely02525 New Member

    i have created one stored procedure its return more than 50 column, but i want to insert only 5 or 6 Column return by this procedure into a Temp table, in my New procedure by excuting previous stored procedure
  2. Jack Vamvas Member

    What about just doing SELECT mycol1,mycol2,mycol3,mycol4,mycol5 FROM myTable, and then INSERTING into the temp table?

    ___________________________________
    Need an IT job? -http://www.ITjobfeed.com

  3. lovely02525 New Member

    actually i am taking value not directly from table ,i am taking value by excuting stored procedure like this

    insert into #rishi exec.<Stored PRocedure> this stored procedure return 50column but i want to insert only 5 column into table
  4. Adriaan New Member

    You cannot choose from the columns returned by your stored procedure - that's a hard fact when it comes to stored procedures, with no work-around ....

    ... except of course if you would create a temp table with all the corresponding columns, and then select the 5 or 6 columns from the temp table. But you knew that already, and you were looking for something else.

    Why is this stored procedure returning more than 50 columns? Is it being used in other parts of your system where you do need all those columns?

    Consider creating a stored procedure that returns exactly the data that you need HERE.

    ***

    Another point is that we don't normally use stored procedures to retrieve a resultset. That is why Jack suggested using a plain old SELECT query, which you can use with INSERT INTO - no problems.
  5. lovely02525 New Member

    Hi Adriaan........
    i Considering What U are Saying,But in My project i can Write another Sp.i have to use same SP.so i am looking that type of solution.on solution is there in your sp u define a temp table with all column which SP returning,and after that what column u want you Select From the temp table.But I am Looking Some Diffrent Solution.








  6. Adriaan New Member

    If you MUST use the existing sp, then the only way is to create a temp table for all the columns returned by the SP ...

    CREATE TABLE #DataFromSP (col1 INT, col2 VARCHAR(100), ..................)

    INSERT INTO #DataFromSP
    EXEC dbo.MySP ....................

    SELECT col1, col3, col35
    FROM #DataFromSP
  7. merrillaldrich New Member

    One argument for Views... <img src='/community/emoticons/emotion-1.gif' alt=':)' />

Share This Page