Search code examples
sql-serversql-updatesp-executesql

sp_executesql to update a temp table

I commonly use, and can easily find documentation on the internet for inserting into a temp table from an sp_executesql command:

 insert into #temp (
    column1,
    column2,
    column3
 )
 exec sp_executesql @myQueryString

However, what I am having trouble finding any documentation on, is how to update the temp table from sp_executesql, such as setting the already existing column2 to a new value from the results of the stored procedure.

Is this possible?

Solution

  • It isn't possible to use the sp_executesql resultset directly in an UPDATE statement.

    You would need to insert the result into a different table variable / temp table / permanent table that you then use to do your update against #temp.

    You can access the temp table directly from the code in @myQueryString though.

    So for example if @myQueryString was UPDATE #temp SET column1 = 2 this would also work.