Create nested stored procedure call to send a uniform result set to Entity Framework

Question

I am working on converting a web forms project to an ASP.NET MVC 4 project.

In this project we the .NET team are not allowed to modify the existing stored procedures, at max we are allowed to write a wrapper for this stored procedure. I.e. We can create an another stored procedure which calls the existing ones.

The existing stored procedure is of the following type

condition 1
  Select A,B,C,D from Table
condition 2
  Select ErrorCode as 100, ErrorMessage as 'Correct that'
condition 3
  Select ErrorCode as 101, ErrorMessage as 'Correct this'
condition 4
  Select ErrorCode as 102, ErrorMessage as 'Not found' from Table

And I am using Entity Framework in this project which requires a complex type to be pre-mapped so for the above type of stored procedure I have declared a complex type as

Complex Type :- SomeComplexType

    string A;
    string B;
    string C;
    string D;

    int ErrorCode;
    string ErrorMessage;

Now my problem is lack of SQL knowledge and syntax, I have been searching a lot on the google and SO, but was not able to find anything on this.

I want to know how to create a wrapper around this existing stored procedure, some thing like the one below.

condition 1
  Select A,B,C,D,ErrorCode as NULL, ErrorMessage as NULL from Table
condition 2
  Select A,B,C,D,ErrorCode as 100, ErrorMessage as 'Correct that' from Table
condition 3
  Select A,B,C,D,ErrorCode as 101, ErrorMessage as 'Correct this' from Table
condition 4
  Select A as NULL, B as NULL, C as NULL, D as NULL,ErrorCode as 102, ErrorMessage as 'Not found' from Table

So if I am able to send data from SQL like shown above I can then handle it easily using Entity Framework's function import and complex type.

How do I check (in the wrapper stored procedure) what result set is returned (from the existing stored procedure)?

Please guide me with ideas on this. Thanks

Answer 1

edit: old post was not helping, adding some more info just to help in the search:

ok, I googled a bit and I really can't find any way to solve your problem; though i found some useful links:

How to Share Data between Stored Procedures and Insert results of a stored procedure into a temporary table

I was hoping that the second one would've been useful but when i tried the OPENROWSET i got this error:

The metadata could not be determined because the statement 'SELECT '1','2','3'' in procedure 'testSP' is not compatible with the statement 'SELECT 100,200' in procedure 'testSP'.

so i suppose it's not the right way.

now, the only ideas I have left are:

• Rewrite the old SP instead of building a wrapper around
• Can you find a way to determine the returned resultset from the input values? if that is possible you can just use if / else to choose the appropriate columns for the temp table where you will insert the data and then build the complex datatype

I'm sorry that i couldn't help any better

edit (again):

the first link says (at the CLR chapter):

As a recap, here are the main situations where INSERT-EXEC fails you, and you would want to turn to the CLR:

The called procedure returns several result sets with different structures. This is true for many system procedures in SQL Server.

so i suggest you to read this chapter, maybe it will be useful