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.
