I imagine that it's at least a little different in other SQL dialects, but with
SQL Server you create a string that begins
create proc ProcName ...
or alter proc ProcName ...
and then execute that SQL statement (running the "alter" version if the proc
already exists) just as one would run a SQL statement like
create index ...
or drop table ...
(by using SQLCommand.ExecuteNonQuery).
I believe that at least one dialect of SQL lets you write
create or update proc ProcName ...
so you don't need to determine if the proc already exists.
I did not (intend to) suggest that the app would "consult the RDBMS to run
create stored procs method". The app would have a "driver" proc that would be
of the form
CreateThisProc();
CreateThatProc();
CreateOtherProc();
and each of those routines would create a single proc. Any parts of the app
that make changes that could affect the stored procs would run the "driver"
proc at completion.
It might make sense to pass a parameter to the driver proc that would let it
run a subset of the procs, e.g. when some particular table changes instead of
running all stored procs you'd only run the ones associated with that table.
But that's almost certainly a premature optimization, as the time to run the
"create the procs" routine is not significant until there are hundreds of procs
At 01:27 AM 9/23/2005, Manoj Aggarwal wrote
>Can you please let me know how to write the stored procedures in code and
>then application consult the RDBMS to run create stored procs method.....
>
>Regards,
>Aggarwal.
>
>-----Original Message-----
>From: Unmoderated discussion of advanced .NET topics.
>[mailto:[EMAIL PROTECTED] On Behalf Of J. Merrill
>Sent: Friday, September 23, 2005 2:25 AM
>To: [email protected]
>Subject: Re: [ADVANCED-DOTNET] Business logic
>
>I've become a believer in never have developers write SPs by hand (in an
>editor). Instead, we write programs (in the language of the app) that
>construct the SPs (as strings) and cause them to be created. This lets you
>have SPs that trivially do things like changing the datatypes of parameters
>or local variables to match the defn of the database. (For example, if a
>column goes from varchar(20) to varchar(25) and the SP variable to
>manipulate it doesn't change, data could get corrupted.)
>
>It also makes it easier to do things that are appropriate in a "code for
>sale" environment -- the code that writes the SP can consult application
>settings to write an SP that works for the current set of application
>settings, rather than having to figure out how the SP can get the setting
>and change its behavior "on the fly" each time it runs.
>
>Any time the schema changes, or an application settings changes, or you
>install a new version (etc), you just run your "create stored procs" method
>and everything is fine.
>
>At 09:15 PM 9/21/2005, Ben Kloosterman wrote (in part)
>>In environments where the SQL is managed by the developers they are a
>>hindrance to the operators who have to add the scrips to production
>>each time they change.
>>
>>Regards,
>>
>> Ben
J. Merrill / Analytical Software Corp
===================================
This list is hosted by DevelopMentor® http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
