Risking the threat of the Anti-Dynamic SQL people (JUST KIDDING!!), I
would
probably create my procedure as follows. Note, there is not error or
parameter checking. If I was putting this into production I would
insure
that those things are checked.
CREATE PROCEDURE OldCFQuery
@SOpt varchar(30),
@SK varchar(30),
@COpt varchar(30) = NULL,
@CBT varchar(30) = NULL
AS
IF @COpt = '--' THEN
SET @COpt = NULL
IF @CBT = '--' THEN
SET @CBT = NULL
DECLARE @lcSQL varchar(1000)
SET @lcSQL = 'SELECT CustomerID,'
+ 'Telephone,AreaCode,'
+ 'BusinessName,Address,'
+ 'City,State,ItemCode,'
+ 'Website,Ad '
+ 'FROM CustomerInformation '
+ 'WHERE 0=0 '
IF @COpt IS NOT NULL
BEGIN
SET @lcSQL = @lcSQL + 'AND City = '''
+ @COpt + ''' '
END
IF @CTB IS NOT NULL
BEGIN
SET @lcSQL = @lcSQL + 'AND Classification = '''
+ @CTB + ''' '
END
set @lcSQL = @lcSQL + 'AND ' + @SOpt
+ ' like ''%' + @SK + '%'' '
+ 'ORDER BY Classification, '
+ @SOpt
EXEC(@lcSQL)
GO
Then you can call the stored procedure the following way:
<CFSTOREDPROC PROCEDURE="OldCFQuery"
DATASOURCE="HTCYP"
BLOCKFACTOR="100">
<CFPROCPARM TYPE="IN"
CFSQLTYPE="CF_SQL_VARCHAR"
VALUE="#FORM.SOpt#">
<CFPROCPARM TYPE="IN"
CFSQLTYPE="CF_SQL_VARCHAR"
VALUE="#FORM.SK#">
<CFIF IsDefined("FORM.COpt")>
<CFPROCPARM TYPE="IN"
CFSQLTYPE="CF_SQL_VARCHAR"
VALUE="#FORM.COpt#">
</CFIF>
<CFIF IsDefined("FORM.CTB")>
<CFPROCPARM TYPE="IN"
CFSQLTYPE="CF_SQL_VARCHAR"
VALUE="#FORM.CBT#">
</CFIF>
<CFPROCRESULT NAME="qResultsOfS">
</CFSTOREDPROC>
Hope this helps!
______________________________________________________
Bill Grover
Supervisor MIS Phone: 301.424.3300 x3324
EU Services, Inc. FAX: 301.424.3696
649 North Horners Lane E-Mail: <mailto:[EMAIL PROTECTED]>
Rockville, MD 20850-1299 WWW: <http://www.euservices.com>
______________________________________________________
> -----Original Message-----
> From: Bosky, Dave [mailto:[EMAIL PROTECTED]]
> Sent: Monday, January 14, 2002 5:00 PM
> To: CF-Talk
> Subject: CF-Query to a sql-server stored procedure..Help
>
>
> I'm trying to convert this cold fusion query to stored
> procedure but can't
> get the correct syntax due to the dynamic where clause.
> Any help would be really cool.
> <CFQUERY NAME="qResultsOfS" DATASOURCE="HTCYP"
BLOCKFACTOR="100">
> SELECT
> CustomerID,Telephone,AreaCode,BusinessName,Address,
> City,State,ItemCode,Website,Ad
> FROM CustomerInformation
> WHERE 0=0
> <CFIF IsDefined("FORM.COpt") AND FORM.COpt is
> not "--">
> AND City = '#FORM.COpt#'
> </CFIF>
> <CFIF IsDefined("FORM.CTB") AND FORM.CTB is not "--">
> AND Classification = '#FORM.CTB#'
> </CFIF>
> AND #FORM.SOpt# like '%#FORM.SK#%'
> ORDER BY Classification, #FORM.SOpt#
> </CFQUERY>
>
> Thanks,
> Dave
>
>
>
>
>
>
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
