0

I am currently working with a stored procedure that performs some background processes and then returns one of two results tables.

If it works ok I get a one column table that says success, if it doesn't then I get a four column table with various error data.

While this is fine if you just execute the code from .net, I now need to execute this from within another stored procedure. While I don't need the output, I do need the background processes to take place. I'd usually insert the output into a table, but can't in this case as the columns in the output varies dependent on the result, and as such cannot define a table that it can insert into.

Easiest answer would be to rewrite the outputs of the background SP to be consistent but this isn't an option. I've even tried wrapping this inside a UDF but the stored procedure can't be called from with a function.

Whatever solution I finally use it must work on versions from SQL Server 2008 R2 up to 2016.

Does anybody have any suggestions?

Many thanks,

Mat.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Matthew Baker
  • 2,637
  • 4
  • 24
  • 49
  • If the procedure has a select statement (which clearly this one does), you can't make it behave differently depending on what calls it. You could change the procedure to behave differently based on the value of a parameter, but of course this would require a change to the procedure and a new parameter. – Sean Lange Nov 03 '16 at 16:50

1 Answers1

1

I would image you could create a SP that inserts the result of the inner SP into a temporary table using the hack below.

Insert results of a stored procedure into a temporary table

If that blocks the ouput then you can return no data.

Community
  • 1
  • 1
Ross Bush
  • 14,648
  • 2
  • 32
  • 55
  • I'm having the same issue as the OP on that link too, The issue was based around the fact that I cannot define the table in advance as the definition changes. – Matthew Baker Nov 07 '16 at 09:45
  • I also cannot use the open row set option as it will be run on several different servers. And short of storing the connection string somewhere in the database an introducing the inherent security risk associated with that, I'm a little stuck. – Matthew Baker Nov 07 '16 at 09:47
  • I'm up voting this because I think it will help some people, but it hasn't quite solved mine. Many thanks. – Matthew Baker Nov 07 '16 at 09:48