I should have mentioned I was trying to convert this query into a
Transact-SQL stored procedure for SQL Server 7. This is the code I have so
far that's not functioning.
-----
CREATE PROCEDURE spSearchResults
--optional : search by city
@COpt [varchar] (50) = NULL,
--optional : search by classification
@CTB [varchar] (80) = NULL,
--required : search option(BusinessName, Address, Telephone)
@SOpt [varchar] (50),
--required : search keyword
@SK [varchar] (80)
AS
SELECT Telephone,AreaCode,BusinessName,Address,City,State,ItemCode,
Classification,Website,Ad
FROM CustomerInformation
WHERE 0=0
IF (@COpt IS NOT NULL) OR (@COpt IS NOT '--')
BEGIN
AND City = @COpt
END
IF (@CTB IS NOT NULL) OR (@CTB IS NOT '--')
BEGIN
AND Classification = @CTB
END
AND @SOpt like '%#FORM.SK#%'
ORDER BY Classification,@SOpt
------
Thanks,
Dave
-----Original Message-----
From: charlie arehart [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 14, 2002 6:38 PM
To: SQL
Subject: RE: Converting query to stored procedure.
Well, you're not going to be able to use the CF code in the stored
procedure. You need to write whatever dynamic aspect using the stored
procedure language of your chosen DBMS (Transact-SQL in SQL Server, PL-SQL
in Oracle, etc.). More than that, since the decisions will be based on
incoming form fields (and the stored procedure won't inherently having
access to that), you'll need to pass those form variables into the stored
procedure (on the call to the SP). Does that help?
/charlie
-----Original Message-----
From: Bosky, Dave [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 14, 2002 4:58 PM
To: SQL
Subject: Converting query to stored procedure.
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
______________________________________________________________________
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
