It's a good idea to avoid choosing variable names
such as "where" and select". These are SQL
reserved words.
Nick
-----Original Message-----
From: Jason Powers [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 09, 2000 11:08 PM
To: CF-Talk
Subject: RE: You are a stored Proc god if you can answer this...
Try preserveSingleQuotes()
-----Original Message-----
From: Mark W. Breneman [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 09, 2000 5:34 PM
To: CF-Talk
Subject: RE: You are a stored Proc god if you can answer this...
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
>>
**********************************************************************
Information in this email is confidential and may be privileged.
It is intended for the addressee only. If you have received it in error,
please notify the sender immediately and delete it from your system.
You should not otherwise copy it, retransmit it or use or disclose its
contents to anyone.
Thank you for your co-operation.
**********************************************************************
------------------------------------------------------------------------------
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.
