How To Get Inheritence Like Functionality from Stored Procedures

Question

How To Get Inheritence Like Functionality from Stored Procedures

YSLGuru

SSC-Insane

Points: 21632
More actions
January 4, 2008 at 11:54 am

#181910

I'm having a brain fart type of day so please excuse me if this is a no-brainer type of question. I can recall reading about this before but darned if I can locate that file in my B-RAM (Brain RAM).
I'm trying to get OOP like functionality from SQL SP's along the lines of Class Inheritence. I'm trying to add functionality to an existing SP without modifying it so that other pieces of code that use it will not break. I don't want to just add more optional parameters to the SP either.
Specifically what I have is an SP that is used by many reports. It is a stock SP from the software company we use and many of the stock reports use this SP. I am creating a custom report that needs to tie out to reports that use this stock SP. The problem is that the custom report needs to include data that is not in the current SP. I could change the stock SP to include this new data however the problem here is that if the software company updates the stock SP I will have to revise my custom version. This is a big problem because updates can come without any notice.
What I'm trying to find is some way to use the stock SP as is and add to it additional data/logic without modifying the current SP.
Is this possible? I swear I remeber seeing/reading about this but darned if I can remeber the specifics.
Thanks a lot in advance to any feedback on this as I'm sure there are many others who would benefit from this.
SQL PLatfiorm = SQL Server 2005
Thanks,
Bubba
Kindest Regards,
Just say No to Facebook!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

David McFarland SSChampion Points: 11815 More actions · Answer 1

Without actually seeing the code, my best suggestion would be to use INSERT/EXEC functionality to populate a temp table, and then combine that with the additional data that you want.

There are sometimes better ways to pass data between stored procs, but most of them involve changes to existing stored proc as well. INSERT/EXEC should be able to work without changes to the original.

YSLGuru SSC-Insane Points: 21632 More actions · Answer 2

David,

Thanks for the reply. I was afraid the answer would be along those lines. I was hoping there might be something in SQL 2005 for this kind of thing without going into using CLR.

Anyone know if SQL 2008 has any changes/enhancements to T-SQL for something like this?

Thanks Again!

Bubba

Kindest Regards,

Just say No to Facebook!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

I'm thinking that if inheritance did exist in SQL Server, it would create a "work" table in TempDB, anyway... what's so terrible about using INSERT/EXEC to populate a temp table and continue from there?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

You might also be able to use something like OPENDATASOURCE...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 5

Jeff Moden (1/4/2008)
You might also be able to use something like OPENDATASOURCE...

Did you mean something like this? (Point 2)

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Madhivanan

Failing to plan is Planning to fail

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

You could call the stock sp from inside the report sp.

You still will need a temp table to hold data returned by the stock sp.

"Keep Trying"

YSLGuru SSC-Insane Points: 21632 More actions · Answer 7

Jeff Moden (1/4/2008)
... anyway... what's so terrible about using INSERT/EXEC to populate a temp table and continue from there?

There's nothing wrong with it at all. I was just looking for something a little more automated/simplified, that's all.

Thanks again for the input.

Bubba

Kindest Regards,

Just say No to Facebook!