locked
Get the value of a column in the parent proc RRS feed

  • Question

  • Hi All,

    I have a stored proc and it is calling another proc. The child proc outputs a result set. I need to get the value of a particular column that exists in the result set returned by the child proc in a local variable which exists in the parent proc.

    Actually I tried this and got an option. But the option inserting the values to a temporary table. I need to define all the columns to create the temporary table. Is there any way like 'SELECT INTO' to avoid writing all the columns to create the table?

    Note: I need the value of only one column. There are totally more than 50 columns returned.


    Thanks,
    Ram


    • Edited by Ram Dhilip Wednesday, June 20, 2012 3:51 PM
    Wednesday, June 20, 2012 3:45 PM

Answers

  • No, there is no other alternative except for OpenQuery which is a beast of its own. You have to define the table correctly.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Wednesday, June 20, 2012 3:53 PM
    • Marked as answer by Ram Dhilip Thursday, June 21, 2012 1:30 PM
    Wednesday, June 20, 2012 3:53 PM

All replies

  • No, there is no other alternative except for OpenQuery which is a beast of its own. You have to define the table correctly.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Wednesday, June 20, 2012 3:53 PM
    • Marked as answer by Ram Dhilip Thursday, June 21, 2012 1:30 PM
    Wednesday, June 20, 2012 3:53 PM
  • Thanks for the reply.

    So I need to define a table with all the 50 columns to get the value of a single column? :(


    Thanks,
    Ram

    Wednesday, June 20, 2012 4:15 PM
  • Yes, precisely. There is no other way.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, June 20, 2012 4:49 PM
  • Describe your scenario in a little more detail, with the two procs if possible?

    We may be getting caught up in the specific wording of your question: As stated, using a proc, you have to define all the columns if you pull back to a temp table.   Rewriting your child proc as a table valued function might give you the results you want, without violating good design principles such as "define your columns" and "never use select *".

    • Proposed as answer by SQL Novice 01 Wednesday, June 20, 2012 6:51 PM
    Wednesday, June 20, 2012 5:16 PM
  • Parent procedure:

    ALTER PROCEDURE [dbo].[ParentProc] ( @Input VARCHAR(11) ) AS BEGIN

    DECLARE @Column1 VARCHAR(50) EXEC ChildProc @Input -- Other codes........ END


    Child Procedure:

    ALTER PROCEDURE [dbo].[ChildProc]
    (
    	@Input	VARCHAR(11)
    )
    AS
    BEGIN
    
         
         SELECT Col1, Col2....., Col50
         FROM   MyTable
         WHERE  myVal = @Input
    
    END

    I want to get the Col1 value which is returned by the SELECT statement from child proc in the local variable @Column1 declared in the parent proc.


    Thanks,
    Ram

    Thursday, June 21, 2012 9:55 AM
  • can you tell us the purpose your variable will serve later in your code(assuming you might be updating other table). you can also have a look at output clause to get the col1 value into your local variable.

    Thanks and regards, Rishabh K

    Thursday, June 21, 2012 10:18 AM
  • I want to use that Col1 value with an IF condition. That is the only purpose.

    Thanks,
    Ram

    Thursday, June 21, 2012 10:49 AM
  • If you're returning a single row, then you can return Col1 value (in addition to the whole row) as an output parameter. Are you able to change child procedure and add the output parameter?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 21, 2012 12:48 PM
  • Yes, the output is a single row only. But I cannot edit the child proc. That is the problem actually. I think your first post is the answer for this. Anyways thanks a lot for all your interest :-)

    Thanks,
    Ram

    Thursday, June 21, 2012 12:55 PM
  • Another alternative is to turn your stored procedure into a function (in other words, create a new inline function that will also return the same row). For the function you can use

    select @Col1 = Column1 from dbo.myNewFunction(@param1)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 21, 2012 12:58 PM
  • That's valuable info. But we are not allowed for any DB object additions/modifications.

    Thanks,
    Ram

    Thursday, June 21, 2012 1:30 PM