none
pass dynamic sql values to other stored procedure

    Question

  • I have two sp's- main_sp & other_sp. other_sp code is written seperately but it accept 3 parameters supplied from main_sp and it is part of main_sp

    alter procedure main_spas

    declare @ReturnTable as table
    (RowId int identity(1,1) primary key, id int, name varchar(100),type varchar(10));

    declare @sqlcommand varchar(max),@typevar varchar(10), @Name varchar(100), @id int, @Loop int, @TotalRows int;


    select @Loop = 0, @TotalRows = 0;
    declare db_cursor for
    select sqlscript from dbo.seltable

    open db_cursor

    fetch next from db_cursorinto into @sqlcommand

    while @@fetch_status=0
    begin

    insert into @returntable(id,name)

    exec (@sqlcommand)
    set @TotalRows = @TotalRows + @@ROWCOUNT;

    fetch next from db_cursor into @sqlcommand


    end
    close db_cursor
    deallocate db_cursor


    while @Loop <=@TotalRows
    BEGIN
    set @Loop  = @Loop + 1;
    select distinct @typevar =a.Type
      from xyz a inner join @returntable b on a.id=b.id
    where b.RowID = @Loop;
    ;
    execute other_sP @id = @id, @name = @name, @typevar = @typevar

    end

    My other stored procedure is like this...The issue here it is inserting field names in datatable instead of values from main_sp

    alter procedure other_sp ( @id int,@name varchar(10),@type varchar(10)) as

    begin

    insert into datatable(id,name,type)

    values (@id,@name,@typevar)

    end

    Thursday, January 30, 2014 9:01 PM

Answers

  • This I don't understand:

    My other stored procedure is like this...*The issue here it is inserting field names in datatable instead of values from main_sp*

    Since in:

    execute other_sP@id= @id,@name= @name,@typevar= @typevar

    You have never set the variables @id and @name, so I would expect you to get NULLs for this columns.

    Then again, there is little need for other_sP at all. Just do:

    INSERT datatable(id, name, type)
       SELECT r.id, r.name, xyz.type
       FROM   @returntable r
       JOIN   xyz ON xyz.id = r.id


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, January 30, 2014 10:31 PM

All replies

  • What do you mean by inserting field names?

    Also, in your other thread I asked you to provide an English description of what you're trying to do, DDL of your tables with some data and desired output. Instead you again gave the code of your SPs which do not make much sense. Please explain what you're trying to do.


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


    My blog


    My TechNet articles

    Thursday, January 30, 2014 9:26 PM
  • This I don't understand:

    My other stored procedure is like this...*The issue here it is inserting field names in datatable instead of values from main_sp*

    Since in:

    execute other_sP@id= @id,@name= @name,@typevar= @typevar

    You have never set the variables @id and @name, so I would expect you to get NULLs for this columns.

    Then again, there is little need for other_sP at all. Just do:

    INSERT datatable(id, name, type)
       SELECT r.id, r.name, xyz.type
       FROM   @returntable r
       JOIN   xyz ON xyz.id = r.id


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, January 30, 2014 10:31 PM
  • I cannot modify other_sp. It is accepting 3 parameters, which am sending. I'm not able to understand why it is inserting filed name as values in table.
    Friday, January 31, 2014 2:22 AM
  • What does execute (@sqlCommand) return in your main SP? Before processing the @ReturnTable can you do select * from @ReturnTable and examine what's in there?

    Also, what is the content of @sqlCommand you're executing in a loop.


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


    My blog


    My TechNet articles

    Friday, January 31, 2014 5:09 AM
  • You have never set the variables @id and @name, so I would expect you to get NULLs for this columns.


    Erland's point is very important

    On your main SP, you should assign some values to these parameters.

    One point with the inner SP (second one), is your type parameter @type or @typevar. Again please check these values.

    alter procedure other_sp ( @id int,@name varchar(10),@type varchar(10)) as
    
    begin
    
    insert into datatable(id,name,type)
    
    values (@id,@name,@typevar)
    
    end
    


    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

    Friday, January 31, 2014 7:23 AM
  • I cannot modify other_sp.

    That does not matter. Just let it lie. You don't need it. Did you test my code?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 31, 2014 8:25 AM