I can't answer your question in regard to SQL 2K Enterprise, but if anyone is interested here is how I do dynamic queries in Oracle. I highly recommend that you put dynamic stored proc calls such as these in their own catch-try blocks as erroneous queries will compile and subsequently may even lock up CF. Debugging these things can be a real pain.
Basically the query is created as a string, output through a refcursor.
**************************************************************************
PROCEDURE get_employee_search_results
(
p_employee_id IN employees.employee_id%TYPE,
p_employer_id IN employers.employer_id%TYPE,
p_lastname IN employees.last_name%TYPE,
p_firstname IN employees.first_name%TYPE,
p_ssn IN employees.ssn%TYPE,
p_employer_name IN employers.name%TYPE,
cur_employee_search_results OUT REFCURSORTYPE,
p_success_flag OUT NUMBER,
p_process_message OUT VARCHAR2
)
IS
v_1 LONG;
v_lastname VARCHAR2(40);
v_firstname VARCHAR2(40);
v_employer_name VARCHAR2(100);
BEGIN
IF instr(p_lastname,CHR(39)) <> 0 THEN
v_lastname := REPLACE(p_lastname,CHR(39),null);
END IF;
IF instr(p_firstname,CHR(39)) <> 0 THEN
v_firstname := REPLACE(p_firstname,CHR(39),null);
END IF;
IF instr(p_employer_name,CHR(39)) <> 0 THEN
v_employer_name := REPLACE(p_employer_name,CHR(39),null);
END IF;
v_1 := 'SELECT /*+FIRST_ROWS */ ee.first_name, ';
v_1 := v_1 || 'ee.last_name, ';
v_1 := v_1 || 'ee.active_flag, ';
v_1 := v_1 || 'ee.employee_id, ';
v_1 := v_1 || 'TO_CHAR(ee.dob,' || CHR(39) || 'MM/DD' || CHR(39) || ') dob, ';
v_1 := v_1 || 'decode(ee.logon_name,null,' || CHR(39) || 'Not Present' || CHR(39) || ',' || CHR(39) || 'Present' || CHR(39) || ') AS logon, ';
v_1 := v_1 || 'SUBSTR(ee.ssn,LENGTH(ee.ssn) - 3, LENGTH(ee.ssn)) ssn, ';
v_1 := v_1 || 'NVL(er.name,' || CHR(39) || 'Unknown' || CHR(39) || ') AS namePart, ';
v_1 := v_1 || 'er.name ';
v_1 := v_1 || 'FROM employees ee, employers er ';
v_1 := v_1 || 'WHERE ee.employer_id = er.employer_id ';
IF p_employee_id IS NOT NULL THEN
v_1 := v_1 || ' AND ee.employee_id =' || p_employee_id;
END IF;
IF p_employer_id IS NOT NULL THEN
v_1 := v_1 || ' AND ee.employer_id =' || p_employer_id;
END IF;
IF p_lastname IS NOT NULL THEN
IF instr(p_lastname,CHR(39)) = 0 THEN
v_1 := v_1 || ' AND UPPER(ee.last_name) LIKE UPPER(' || CHR(39) || p_lastname || '%' || CHR(39) || ')';
ELSE
v_1 := v_1 || ' AND UPPER(REPLACE(ee.last_name,CHR(39),null)) LIKE UPPER(' || CHR(39) || v_lastname || '%' || CHR(39) || ')';
END IF;
END IF;
IF p_firstname IS NOT NULL THEN
IF instr(p_firstname,CHR(39)) = 0 THEN
v_1 := v_1 || ' AND UPPER(ee.first_name) LIKE UPPER(' || CHR(39) || p_firstname || '%' || CHR(39) || ')';
ELSE
v_1 := v_1 || ' AND UPPER(REPLACE(ee.first_name,CHR(39),null)) LIKE UPPER(' || CHR(39) || v_firstname || '%' || CHR(39) || ')';
END IF;
END IF;
IF p_ssn IS NOT NULL THEN
v_1 := v_1 || ' AND ee.ssn LIKE ' || CHR(39) || '%' || p_ssn || '%' || CHR(39);
END IF;
IF p_employer_name IS NOT NULL THEN
IF instr(p_employer_name,CHR(39)) = 0 THEN
v_1 := v_1 || ' AND UPPER(er.name) LIKE UPPER(' || CHR(39) || p_employer_name || '%' || CHR(39) || ')';
ELSE
v_1 := v_1 || ' AND UPPER(REPLACE(er.name,CHR(39),null)) LIKE UPPER(' || CHR(39) || v_employer_name || '%' || CHR(39) || ')';
END IF;
END IF;
OPEN cur_employee_search_results FOR v_1;
p_success_flag := 1;
p_process_message := 'Query successful';
EXCEPTION
WHEN OTHERS THEN
p_success_flag := 0;
p_process_message := 'Query failure: ' || SUBSTR(SQLERRM,1,150);
END get_employee_search_results;
*******************************************************
[Lonny Eckert] -----Original Message-----
From: Tangorre, Michael [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 5:57 AM
To: CF-Talk
Subject: Query to Stored Proc
How would you rework the logic in the WHERE clause to get this query put
into a stored proc?
Can you put conditional logic in the WHERE clause inside a stored proc?
SQL 2K Enterprise
<cfquery name="selectOpportunityResults" datasource="otis">
SELECT
O.opportunityId,
O.title,
O.lastUpdateUserId,
O.lastUpdateOn,
O.createDate,
U1.firstName AS creatorFirstName,
U1.lastName AS creatorLastName,
U1.emailAddress AS creatorEmailAddress,
U2.firstName AS updaterFirstName,
U2.lastName AS updaterLastName,
U2.emailAddress AS updaterEmailAddress,
P.phaseName
FROM
tbl_opportunity O
LEFT JOIN tbl_user U1 ON (O.createUserId = U1.userId)
LEFT JOIN tbl_user U2 ON (O.lastUpdateUserId = U2.userId)
LEFT JOIN tbl_phase P ON (O.phaseId = P.phaseId)
WHERE
1=0
<cfif form.rfpNumber NEQ "">
OR O.rfpNumber = '#form.rfpNumber#'
</cfif>
<cfif form.title NEQ "">
OR O.title = '#form.title#'
</cfif>
<cfif form.naicsCode NEQ "">
OR O.naicsCode = '#form.naicsCode#'
</cfif>
<cfif form.costCenterLocation NEQ "-1">
OR O.costCenterId = '#form.costCenterLocation#'
</cfif>
<cfif form.procurementType NEQ "-1">
OR O.procurementTypeId = '#form.procurementType#'
</cfif>
<cfif form.awardType NEQ "-1">
OR O.awardTypeId = '#form.awardType#'
</cfif>
<cfif form.classification NEQ "-1">
OR O.classificationId = '#form.classification#'
</cfif>
<cfif form.phase NEQ "-1">
OR O.phaseId = '#form.phase#'
</cfif>
<cfif form.clientAgency NEQ "-1">
OR O.agencyId = '#form.clientAgency#'
</cfif>
<cfif form.clientBureau NEQ "-1">
OR O.bureauId = '#form.clientBureau#'
</cfif>
<cfif form.clientBureauOther NEQ "-1">
OR O.bureauOtherId = '#form.clientBureauOther#'
</cfif>
ORDER BY
O.createDate DESC
</cfquery>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
