how do i capture the result set from a query into a variable @SQLstr so I can exec(@SQLstr)

• jpgillum

  Old Hand

  Points: 326

  More actions

  April 25, 2016 at 1:40 pm

  #147656

  I have the following SQL that creates the output I need to capture in a variable like @SQLstr so that I can execute the string:

  exec(@SQLstr)

  But I get the error message:

  Msg 116, Level 16, State 1, Line 29

  Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

  When I try to set @SQLstr = the select statement. See below.

  The result set is a number of update statement.

  Any ideas??

  ---- Declare Variables

  declare @Utablename varchar(200) = 'JP_CDM.case_reopen_block_history'

  declare @UtableKey varchar(200) = 'case_reopen_block_history_id'

  declare @Wtablename varchar(200) = 'case_reopen_block_history_insert_hld'

  declare @WtableKey varchar(200) = 'case_reopen_block_history_id'

  declare @WtableKeyV int = 1

  declare @SQLstr varchar (4000)

  declare @get_case_key varchar(35) = 'case_id'

  declare @case_id int = 1

  declare @surrogate_Pkey_str varchar(100) = @Utablename + '.' + 'case_id'

  Set @SQLstr =

  (select ' UPDATE ' + @Utablename as ' ',

  + ' SET ' as ' ',

  + @Utablename + '.' + column_name as ' ',

  + ' = ' as ' ',

  + 'work' + '.' + @Wtablename + '.' + column_name as ' ',

  + ' from ' + 'work' + '.' + @Wtablename as ' ',

  + ' where ' + @surrogate_Pkey_str as ' ',

  + ' = ' + convert(varchar (100), @WtableKeyV) as ' '

  FROM information_schema.columns isc

  where table_schema = 'work'

  and column_name <> 'case_id'

  and Table_name = @Wtablename)

  The result set contains the update statements I need to execute:

  Example:

  UPDATE JP_CDM.case_reopen_block_history SET JP_CDM.case_reopen_block_history.date_reopen_block_from_date = work.case_reopen_block_history_insert_hld.date_reopen_block_from_date from work.case_reopen_block_history_insert_hld where JP_CDM.case_reopen_block_history.case_id = 1

  UPDATE JP_CDM.case_reopen_block_history SET JP_CDM.case_reopen_block_history.date_reopen_block_thru_date = work.case_reopen_block_history_insert_hld.date_reopen_block_thru_date from work.case_reopen_block_history_insert_hld where JP_CDM.case_reopen_block_history.case_id = 1

  UPDATE JP_CDM.case_reopen_block_history SET JP_CDM.case_reopen_block_history.record_state = work.case_reopen_block_history_insert_hld.record_state from work.case_reopen_block_history_insert_hld where JP_CDM.case_reopen_block_history.case_id = 1

• Lowell

  SSC Guru

  Points: 323522

  More actions

  April 25, 2016 at 1:45 pm

  #1874367

  duplicate post.

  original discussion is here, lets keep it all together to prevent fragmentation of replies:

  http://www.sqlservercentral.com/Forums/Topic1779526-338-1.aspx

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!