Are you calling the stored procedure via the cfquery tag, the cfstoredproc
tag, or from within another stored procedure?
Could you post the snippet of your code that calls the procedure? That
would give us a better idea on how to work this out.
Chris
----------------------------------------------
Original Message
From: "Mark W. Breneman"<[EMAIL PROTECTED]>
Subject: RE: You are a stored Proc god if you can answer this...
Date: Mon, 9 Oct 2000 16:34:26 -0500
>No luck...
>
>Any other ideas?
>
>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 4:23 PM
>To: CF-Talk
>Subject: RE: You are a stored Proc god if you can answer this...
>
>
>Try:
>Search_RC1('WHERE State=''NC''')
>
>Those are 2 single quotes around the NC. You need to enclose NC in a
>single quote, but since the enire expression has to be enclosed in single
>quotes, you have to escape them with yet another pair of single quotes.
>
>Hope that helps,
>Chris
>
>----------------------------------------------
>Original Message
>From: "Mark W. Breneman"<[EMAIL PROTECTED]>
>Subject: RE: You are a stored Proc god if you can answer this...
>Date: Mon, 9 Oct 2000 15:42:10 -0500
>
>>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.
>>
>
>_____________________________________________
>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.
>
_____________________________________________
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.
