Mark,
I do not know if you got an answer, trying to catch up on my reading after
being on the road a lot. In my experience with Oracle stored procedures, you
can set them up to receive a parameter not a whole WHERE clause.
SELECT *
FROM TABLE_A
WHERE STATE = '&1'
What happens here is that the '&1' is interpreted as a literal. Then when
you execute the procedure you do something like this:
EXECUTE <STOREDPROC> NC
Peter
-----Original Message-----
From: Mark W. Breneman [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 09, 2000 16:42
To: CF-Talk
Subject: RE: You are a stored Proc god if you can
answer this...
OK, one problem...
How do I get around the quote problems.
IF I try (one at a time):
Search_RC1("WHERE State="NC"")
Search_RC1('WHERE State='NC'')
Search_RC1("WHERE State='NC'")
Search_RC1('WHERE State="NC"')
I get:
Line 1: Incorrect syntax near 'WHERE State='NC''.
Line 1: Incorrect syntax near 'WHERE State='.
Line 1: Incorrect syntax near 'WHERE State='NC''.
Line 1: Incorrect syntax near 'WHERE State="NC"'.
Is to possible to pass quotes to a stored proc and build a
query in this
fashion.
Am I missing something?
Thanks
Mark W. Breneman
-Cold Fusion Developer
-Network Administrator
Vivid Media
[EMAIL PROTECTED]
www.vividmedia.com
608.270.9770
-----Original Message-----
From: Chris Terrebonne [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 09, 2000 2:32 PM
To: CF-Talk
Subject: Re:You are a stored Proc god if you can answer
this...
Try this:
CREATE PROCEDURE [Search_RC] @where varchar(255)
AS
DECLARE @Select varchar(255)
SET @Select = 'SELECT count (*) as noofrecords FROM
NPO_IRS_primary ' +
@where
CREATE TABLE #x (MyCount int)
INSERT INTO #x
EXECUTE(@Select)
SELECT MyCount FROM #x
DROP TABLE #x
----------------------------------------------
Original Message
From: "Mark W. Breneman"<[EMAIL PROTECTED]>
Subject: You are a stored Proc god if you can answer this...
Date: Mon, 9 Oct 2000 12:09:13 -0500
>I need to do a record count with a CF generated "WHERE"
statement.
>
>SELECT count (*) as noofrecords
>FROM xxx
>#PreserveSingleQuotes(searchstring)#
>
>Now I need to convert this into a stored proc.
>
>I tried:
>CREATE PROCEDURE [Search_RC] @where varchar(255)
>AS
>
>SELECT count (*) as noofrecords
>FROM NPO_IRS_primary
>@where
>
>No luck...
>
>Any one have an idea?
>
>
>
>Example of the "WHERE" statement:
>WHERE (State = 'NC') and ((ActivityCodeOne = '260') or
(ActivityCodetwo =
>'260') or (ActivityCodethree = '260') or (ActivityCodeOne
> = '261') or (ActivityCodetwo = '261') or
(ActivityCodethree = '261') or
>(ActivityCodeOne = '262') or (ActivityCodetwo = '262') or
(ActivityCodethree
>=
> '262') or (ActivityCodeOne = '263') or (ActivityCodetwo =
'263') or
>(ActivityCodethree = '263') or (ActivityCodeOne = '264') or
(ActivityCodetwo
>=
> '264') or (ActivityCodethree = '264') or (ActivityCodeOne
= '265') or
>(ActivityCodetwo = '265') or (ActivityCodethree = '265') or
(ActivityCodeOne
>=
> '266') or (ActivityCodetwo = '266') or (ActivityCodethree
= '266') or
>(ActivityCodeOne = '267') or (ActivityCodetwo = '267') or
(ActivityCodethree
>=
> '267') or (ActivityCodeOne = '268') or (ActivityCodetwo =
'268') or
>(ActivityCodethree = '268') or (ActivityCodeOne = '269') or
(ActivityCodetwo
>=
> '269') or (ActivityCodethree = '269') or
(ActivityCodeOne =
'279')
>or (ActivityCodetwo = '279') or (ActivityCodethree = '279')
)
>
>
>
>Mark W. Breneman
>-Cold Fusion Developer
>-Network Administrator
> Vivid Media
> [EMAIL PROTECTED]
> www.vividmedia.com
> 608.270.9770
>
>---------------------------------------------------------------------------
---
>Archives:
http://www.mail-archive.com/cf-talk@houseoffusion.com/
>To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send
>a message to [EMAIL PROTECTED] with
'unsubscribe' in the
body.
>
_____________________________________________
Free email with personality! Over 200 domains!
http://www.MyOwnEmail.com
----------------------------------------------------------------------------
--
Archives:
http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with
'unsubscribe' in
the body.
----------------------------------------------------------------------------
--
Archives:
http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
