I'd say you are being ultra causious if your app is managing the SP's mthat
it is using. I have never come across a system that will create an SP it
needs if it doesn't exist. Is it likely to get deleted?
If you really want to pursue down this path then I suggest you take a step
away from CF to start with and write an SP that will create SP's on the fly.
Therefore it will need to accept a load of parameters in order to make it
generic including server,database,owner,spname,parameter list, procedurecode
(bulk of procedure) at a minimum.
You could even look at it a step further and say it's all very well
recreating SP's if someones deleted them but what if they've deleted tables
that the SP uses as well? A far better solution is to place tight
permissions on the entities within your db so that people can't accidiently
delete or change them unless granted permission to do so.
Don't get me wrong, I think that a utility enabling people to create there
own SP's could be useful in some circumstances, I just don't think it should
be there to create SP's if they don't exisit. For the code that you are
talking about it's almost worth not using SP's at all if you think they are
going to get deleted on a regular basis (you were only doing 1 insert). If
you are having to recreate the SP and then run it you are pretty much losing
the benefits of having an SP in the first place if you are regularly
recreating it because it has been deleted.
-----Original Message-----
From: Andrew Scott [mailto:[EMAIL PROTECTED]]
Sent: 16 July 2001 16:51
To: CF-Talk
Subject: RE: Stored Procedures
The idea that I am using is that the application becomes self
sufficient. With this I am looking at creating the sp on the fly in case
they are removed or deleted by accident.
The idea is that a cfml script is used to go through and check the sp,
if it doesn't exist to then create it... Your right I might be a little
bit ambitious with this, but I was hoping to cut the work out of setting
up everything manually...
Do you now of any good references on this subject!
> -----Original Message-----
> From: Andy Ewings [mailto:[EMAIL PROTECTED]]
> Sent: Monday, 16 July 2001 11:50 PM
> To: CF-Talk
> Subject: RE: Stored Procedures
>
> I'm not sure why you are trying to do what you want to do. By the
looks of
> it what you actually want to do is to creat an SP to insert a new
users
> details into the db. If so you don't do this using CF. You run the
SQL you
> have in SQL Query Analyser against the DB you want it to affect and
then you
> call the SP in your CF by either using CFSTOREDPROC (which you should)
or by
> using CFQUERY and running the SQL "EXEC sp_AddUser param1 param2
param3
> etc......." (not recomended). In doing this you have tightened up
security
> and the SQL is cached so an execution plan exists and hence the SQL
doesn't
> need to be parsed.
>
> If however you are trying to write a generic web based utility whereby
users
> can create there own sp's then you are on the write traks although you
> should have an SP (which you write) which accepts parameter in order
to
> create other sp's within your db although this is not simple and
certainly
> challenging if you are only starting out with SP's!
>
> -----Original Message-----
> From: Andrew Scott [mailto:[EMAIL PROTECTED]]
> Sent: 16 July 2001 14:26
> To: CF-Talk
> Subject: RE: Stored Procedures
>
>
> Which is what I am looking for, if it doesn't exist to create it... I
am
> new to storedprocs. Although I know the syntax I haven't used them
much
> with CF yet...
>
>
> > -----Original Message-----
> > From: Andy Ewings [mailto:[EMAIL PROTECTED]]
> > Sent: Monday, 16 July 2001 11:14 PM
> > To: CF-Talk
> > Subject: RE: Stored Procedures
> >
> > Never tried it, you may have to put semi colons at the end of every
> sql
> > statement if ODBC terms them as separate SQL stements. I would
> suggest that
> > you write and call an SP that creates SP's for you.
> >
> > -----Original Message-----
> > From: Andrew Scott [mailto:[EMAIL PROTECTED]]
> > Sent: 16 July 2001 14:09
> > To: CF-Talk
> > Subject: RE: Stored Procedures
> >
> >
> > So you are saying I could do this then....
> >
> > <cfquery name="sp_Adduser" datasource="datasource">
> > IF not EXISTS (SELECT * FROM sysobjects WHERE name =
> > "sp_Adduser1" AND type = "P")
> > CREATE PROCEDURE sp_AddUser
> > @username varchar(50),
> > @password varchar(12),
> > @Firstname varchar(50),
> > @Familyname varchar(50),
> > @ID varchar(50),
> > @ID_Member varchar(50) output
> > AS
> > set nocount on
> > if not exists (select ID_Member from Members where Username =
> > @username)
> > begin
> > insert into
> > Members(ID_Member,Username,Password,Firstname,Familyname)
> > values (@ID,@username,@password,@Firstname,@Familyname)
> > select @ID_Member = @ID
> > end
> > else
> > select @ID_Member = -1
> > return
> > END
> > </cfquery>
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
