> I thought I would ask the list a question with Store Procs
> andf CF, I was working on a few scripts and was wondering
> is it possible for CF to detect that the Proc doesn't exist
> and create the proc or even if it can be done within a stored
> Procedure...
Yes, this is generally possible. CF can run any kind of SQL statement you
want, including data definition statements, not just data manipulation
statements. The syntax used to check for the existence of something within
the database will vary from one database platform to another. Here's an
example in T-SQL that would work in SQL Server 7:
<cfquery name="qAddSP" datasource="ds">
if exists (select * from sysobjects where id =
object_id(N'[dbo].[getAnEmailAddress]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[getAnEmailAddress]
GO
CREATE PROCEDURE getAnEmailAddress
@theUserName varchar (255)
AS
SELECT Email
FROM UserInfo
WHERE UserName = @theUserName
</cfquery>
Note that this example is a little different from what you asked for, but
illustrates the fact that you can write DDL within an SQL batch and run it
through CFQUERY. This example checks for the existence of a specific stored
procedure, and if it does exist, it drops it. Then, in either case, it
builds the stored procedure.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
~~~~~~~~~~~~~ Paid Sponsorship ~~~~~~~~~~~~~
Get Your Own Dedicated Win2K Server! Instant Activation for $99/month w/Free
Setup from SoloServer PIII600 / 128 MB RAM / 20 GB HD / 24/7/365 Tech Support
Visit SoloServer, https://secure.irides.com/clientsetup.cfm.
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
