Insert with exec and query

• Si

  SSCommitted

  Points: 1537

  More actions

  July 20, 2006 at 5:33 am

  #114300

  Hi, could someone please let me know how I can code an insert statement which both exec's an SP and runs a statement.  I want to be able to do something like -

  exec sp_MSforeachdb @command1 =

  'insert #temptable EXEC master.dbo.sp_helprotect @username = ''public''  ,  select db_name()'

  So that the temp table contains the result of sp_helpprotect and the db name

  Thanks very much in anticipation

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  July 20, 2006 at 7:18 am

  #650365

  Add dbname column to end of your #temptable and try this

  exec sp_MSforeachdb @command1 = 'USE [?]'

  ,@command2='insert #temptable (Owner,Object,Grantee,Grantor,ProtectType,[Action],[Column]) EXEC master.dbo.sp_helprotect @username = ''public'''

  ,@command3='update #temptable set dbname=''?'' where dbname is null'

  Far away is close at hand in the images of elsewhere.
  Anon.

• Si

  SSCommitted

  Points: 1537

  More actions

  July 20, 2006 at 9:16 am

  #650425

  Thats just what I wanted, thanks David!