Embedded Stored Procedure within App

23 Nov 2006 7:51 PM

Spam Catcher

Hi all,

We have an application with is store procedure dependent. I'm looking for a
way to simplify our deployment by embedding Stored Procedures within our
application so that we do not have to "keep track" of which SPs to promote.
Basically if we can just "build and release" that will be idea.

So how do you guys manage your stored procedures? I'm thinking of two
possiblities:

1. Store the SPs in a the resouce file

2. Store the SQL scripts in a folder, then dynamically load the SPs when
they're needed.

I like Option #1 because all required SPs are deployed with the binary...
but editing a resource file is pain with the default VS.NET editor.

Option #2 provides a lot of flexiblity - we can modify SPs to suit certain
customer requirements... but synchronizing releases will be difficult.

Any other ideas?

Thanks!

23 Nov 2006 8:05 PM

lord.zoltar

Spam Catcher wrote:
Show quoteHide quote

> Hi all,
>
> We have an application with is store procedure dependent. I'm looking for a
> way to simplify our deployment by embedding Stored Procedures within our
> application so that we do not have to "keep track" of which SPs to promote.
> Basically if we can just "build and release" that will be idea.
>
> So how do you guys manage your stored procedures? I'm thinking of two
> possiblities:
>
> 1. Store the SPs in a the resouce file
>
> 2. Store the SQL scripts in a folder, then dynamically load the SPs when
> they're needed.
>
> I like Option #1 because all required SPs are deployed with the binary...
> but editing a resource file is pain with the default VS.NET editor.
>
> Option #2 provides a lot of flexiblity - we can modify SPs to suit certain
> customer requirements... but synchronizing releases will be difficult.
>
> Any other ideas?
>
> Thanks!

I go with option 3:
Build a seperate Update utility that has ALL the stored procedures
(internally, as resources). then it can check the database to see which
updates have already been applied, then it applies the ones it needs.

23 Nov 2006 8:28 PM

Spam Catcher

lord.zol***@gmail.com wrote in

news:1164312350.190385.193260@m7g2000cwm.googlegroups.com:

> I go with option 3:
> Build a seperate Update utility that has ALL the stored procedures
> (internally, as resources). then it can check the database to see
> which updates have already been applied, then it applies the ones it
> needs.

But how do you track the version of a SP?

What if the SP was purposefuly customized for a customer?

Are there custom properties that can be used to flag these sort of details?

23 Nov 2006 8:33 PM

lord.zoltar

Spam Catcher wrote:
Show quoteHide quote

> lord.zol***@gmail.com wrote in
> news:1164312350.190385.193260@m7g2000cwm.googlegroups.com:
>
> > I go with option 3:
> > Build a seperate Update utility that has ALL the stored procedures
> > (internally, as resources). then it can check the database to see
> > which updates have already been applied, then it applies the ones it
> > needs.
>
> But how do you track the version of a SP?
>
> What if the SP was purposefuly customized for a customer?
>
> Are there custom properties that can be used to flag these sort of details?

Ahh! all good questions! These issues have not actually been resolved
(this tool was just started last Friday! ;)).
But yes, I will have to deal with these some time soon. I'm not too
worried about SP versioning, for my app it's ok to drop an old version
and just cram the new one in. If that's not OK, maybe just a comment at
the begining of the stored procedure saying
"Version:1.4.2" or something?
Hmm I guess I have some research to do...

23 Nov 2006 9:09 PM

Spam Catcher

lord.zol***@gmail.com wrote in
Show quoteHide quote

news:1164313995.039742.112750@l12g2000cwl.googlegroups.com:

>> But how do you track the version of a SP?
>>
>> What if the SP was purposefuly customized for a customer?
>>
>> Are there custom properties that can be used to flag these sort of
>> details?
>
> Ahh! all good questions! These issues have not actually been resolved
> (this tool was just started last Friday! ;)).
> But yes, I will have to deal with these some time soon. I'm not too
> worried about SP versioning, for my app it's ok to drop an old version
> and just cram the new one in. If that's not OK, maybe just a comment
> at the begining of the stored procedure saying
> "Version:1.4.2" or something?

Ya I'm thinking of tagging my SPs - perhaps wrapping the SP in a XML
container.

Personally I don't like SPs - but some of our integrators are not
programmers... and SP scripting is the closest they'll come to programming
with an API :S