sql server - use sql stored procedure to insert data which is returned from a query stored in a table -

sql server 2005 have stored procedure used insert data table. of data need come results of executing query stored in separate table.

the main problem keep hitting not being able execute returned query. have tried creating several functions on past couple of days based on other posts have read, keep hitting sql errors exec, execute, sp_executesql, etc.

i going paste several scripts can use replicate environment. hoping can please provide actual code sample execute returned query use within stored proc insert function.

thank you!!!

    create table [dbo].[client](     [cli_id] [int] identity(1,1) not null,     [cli_first_name] [varchar](100) null,   constraint [pk__client__07f6335a] primary key clustered  (     [cli_id] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on, fillfactor = 90) on [primary] ) on [primary]  go  set ansi_padding off go   insert client (cli_first_name, cli_last_name) values ('tom', 'smith'); go    create table [dbo].[assessment_datalabel_list](     [adl_id] [int] identity(1,1) not null,     [name] [nvarchar](50) not null,     [boundname] [nvarchar](50) not null,     [query] [ntext] not null,     [keyfieldname] [nvarchar](50) not null,     [status] [nvarchar](20) not null,  constraint [pk_assessment_datalabel_list] primary key clustered  (     [adl_id] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [primary] ) on [primary] textimage_on [primary]  go  insert assessment_datalabel_list (name, boundname, query, keyfieldname, status)  values ('name, first', 'cli_first_name', 'select isnull(cli_first_name,'''') cli_first_name client cli_id = @keyfieldvalue', 'cli_id', 'active') go  insert assessment_datalabel_list (name, boundname, query, keyfieldname, status)  values ('name, last', 'cli_last_name', 'select isnull(cli_last_name,'''') cli_last_name client cli_id = @keyfieldvalue', 'cli_id', 'active') go   create table [dbo].[item_source] (        [item_id] [int] identity(1,1) not null,     [itemtype] [nvarchar](50) not null,     [itemcaption] [nvarchar] (50) null,       [adl_id] [int] not null ) go  insert item_source (itemtype, itemcaption, adl_id) values  ('datalabel', 'first name',1) go  insert item_source (itemtype, itemcaption, adl_id) values  ('datalabel', 'last name',2) go  create table [dbo].[item_destination] (     [itemtype] [nvarchar](50) not null,     [itemcaption] [nvarchar] (50) null,       [itemvalue] [nvarchar](50) null ) go   create procedure [dbo].[spinsertstuff]  @cli_id int   insert item_destination (itemtype, itemcaption, itemvalue) select itemtype, itemcaption, [[[ value of executed query adl table --- dbo.functiontogetresultsofstoredquery(item_source.adl_id, @cli_id) ]]] item_source item_source.item_id in (1,2)  -- insert insert both item_source rows item_dest 1 call.  first row should have itemvalue of tom, second row should have itemvalue of smith go 

you may check this fiddle

the code of stored procedure is:

create procedure [dbo].[spinsertstuff]  @cli_id int    declare @sql varchar(max) declare @adl_id int  declare mycursor cursor  select query, adl_id assessment_datalabel_list  open mycursor    fetch next mycursor @sql, @adl_id     while @@fetch_status = 0    begin             set @sql = replace(@sql,'@keyfieldvalue',@cli_id)         declare @temp table ([value] [nvarchar](50))          insert @temp         exec (@sql)          insert item_destination         (itemtype, itemcaption, itemvalue)         select         itemtype, itemcaption, (select [value] @temp)         item_source          item_source.adl_id = @adl_id             delete @temp          fetch next mycursor @sql, @adl_id    end     close mycursor    deallocate mycursor  go