Executing prcedure inside a procedure with dynamic sql

• whole

  SSC-Addicted

  Points: 436

  More actions

  September 22, 2011 at 10:10 am

  #248354

  Hi, i am having issues excuting procedure within procedure.

  here is my code

  i have my variable set as

  set @DBString = '[' + @Server + '].[' + @Database + '].[dbo].'

  this is because we are getting the server details and database details dynamcally based on data.

  and i have my temp table which needs to get populate from the a procedure in other database with a parameter.

  insert into #MyReasonCodes exec @DBString + reasoncodes_getdetailsfortype 'CA'

  but running is giving me syntax error like

  Msg 102, Level 15, State 1, Procedure ValidatePODataInIRMA-temp-1, Line 266

  Incorrect syntax near 'reasoncodes_getdetailsfortype'.

  please help me correct this one. and show me the best way to execute a procedure with above scenario in a procedure.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  September 22, 2011 at 10:16 am

  #1385284

  DECLARE @DBString VARCHAR(100)

  SET @DBString = 'svr.dbname.dbo.'

  insert into #MyReasonCodes (please list the columns)

  exec(@DBString + 'reasoncodes_getdetailsfortype ''CA''')