July 11, 2006 at 2:48 am
Hi,
I have stored procedure "SomeProc @arg0 int, @arg1 nvarchar(260)". This procedure return 3 tables. Procedure is used by client app. Procedure and app is from third party company, and I haven't chance to change them.
I need change behaviour of the "SomeProc". The result should be UNION of result tables from two execution of the "SomeProc". Once with original parameters and once with adapted parameters. I thought the right solution is rename "SomeProc" to "SomeProc__orig", create new procedure "SomeProc" with same signature, and in "SomeProc" execute "SomeProc__orig" twice.
But I met the problem with union of results from "SomeProc__orig". Standard practice "INSERT INTO #MyTemporaryTable EXEC SomeProc__orig @arg0, @arg1" can not be used ( or I don't know how ). And if I simply execute the "SomeProc__orig" twice, the result are six tables.
How can I resolve this?
Thanks!
July 11, 2006 at 4:03 am
Jakub
You can't insert the results into a table, because the procedure returns more than one result set. And you can't do a UNION of the results for the same reason.
As I see it, you have three choices:
(1) Get the third party to rewrite the proc, or allow you to rewrite it.
(2) Have the front end do the processing of the result sets for you.
(3) Dump the result sets of the two executions to text files and write something in VB or similar to manipulate them into the format you want.
Good luck
John
July 11, 2006 at 7:40 am
If you can do this then you can surely amend the proc (or a copy of it) so as to return the UNION of the rowsets?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 11, 2006 at 8:36 am
The original "SomeProc" can not be modified. The source code is not accessible and I'm not 100% sure, what is content of original "SomeProc".
July 12, 2006 at 4:17 am
I solved it. But the solution is not in TSQL. I used CLR integration and write stored procedure with union results in c#.
July 12, 2006 at 1:28 pm
Effectively the same as
(2) Have the front end do the processing of the result sets for you.
Except you are using a middle tier (the CLR code) rather than a front end.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply