Exec StoreProcedure inside another and get both output values and result set

• sarathcd

  SSC Journeyman

  Points: 91

  More actions

  September 30, 2014 at 2:53 am

  #311226

  Hi,

  I have a scenario where I have to execute a storedprocedure inside another storedprocedure and get both the output variable values of the storedprocedure executed inside and the resultset of it. I am using the below code to get the resultset, but not sure how to get the output values from it (I have specified the current implementation at the end.).

  One of the reasons we are following the below approach is that we did not want to specify the columns returned in the resultset as there are many of them and they are prone to changes.

  Requirement:

  We are caching the website in Cdn, we need to clear the cache whenever the StoredProcedure response changes. For the stored procedure response we are calculating the Checksum for each row and saving it into a checksum table. And for any change in the checksum calculated we are clearing the Cdn cache.

  Our current implementation uses the below:

  EXEC('INSERTINTO #tablechecksum (columns)

  SELECTcolumns FROM

  OPENROWSET

  (

  ''SQLNCLI'',

  ''Server=' + @ServerName + ';Database=' + @DbName + ';Trusted_Connection=yes;'',

  ''SET FMTONLY OFF

  EXEC sp_getvalues @InputVariable1 ='''' + @InputVariable1 +

  ''''' , @OutputVariable1 = ''''' + @OutputVariable1

  )