I'll address the "specific" of the second query here,
two points here:
1) I would move <cfif px is "search"> sort of logic out of the query itself, else, if the given table has millions of rows you want to return all? the output query MAXROWS attribute is not necessary a desirable practice, and on the other side, what if "search" condition is met, how does the "MAXROWS" attribute is going to play here ... in addition to the idea/concept of the separation of logic and query ...
2) it's easy to write a procedure to solve this problem, the "KEY" word is null, but setting parameter/argument default value to null is a starter but not good enough. Ponder on that, but if you don't want to do that, either follow up here or send me an email.
Cheers,
Don
> -----Original Message-----
> From: Pratte, Jeff [mailto:[EMAIL PROTECTED]
> Sent: Friday, October 08, 2004 2:06 PM
> To: CF-Talk
> Subject: SQL stored procedure question
>
> OK, after hearing everybody say don't use Access and an converting my
> Check Request System to MS SQL Server. And I am trying to use Stored
> Procedures. However, I am finding it more difficult then I anticipated.
> For instance, I used to have a query like this:
>
> <cfquery Name="qrySignoffRequest" datasource="#application.SQLDB#"
> blockfactor="100">
> select crID, SOffUser, SOffNote
> from qrySignoffRequest
> where SOffDecision = 'Open' and SOffUser = '#theUser#'
> order by #sort5#
> </cfquery>
>
> My stored procedure attempt was this:
>
> CREATE PROCEDURE SignOffCheckRequest_Getx_byDecision_SoffUser
> @SoffUser varchar(10),
> @Decision varchar(10),
> @Sort varchar(10)
> AS
> select crID, SOffUser, SOffNote
> from viewSignoffCheckRequest
> where SOffDecision = @Decision
> and SOffUser = @SoffUser
> Order by CASE
> WHEN @Sort = 'crID' THEN crID
> WHEN @Sort = 'crDate' THEN crDate
> WHEN @Sort = 'crVendor' THEN crVendor
> WHEN @Sort = 'crChkAmt' THEN crChkAmt
> WHEN @Sort = 'SOffUser' THEN SOffUser
> END
> GO
>
> It worked for every type of sort except one (crVendor) where it said
> that it was trying to convert an nvarchar to a float. What's that all
> about?
>
> Then we come to this query:
>
> <cfquery Name="qryCOA" datasource="#application.ChartOfAccountsDB#"
> username="abc" password="xyz">
> select * from WEBMASTER.AP_COA
> where 1=1
> <cfif px is "search">
> <cfif isDefined("searchCO") and searchCO gt
> "">and ldr_entity_id like '#searchCO#%'</cfif>
> <cfif isDefined("searchPRIME") and searchPRIME
> gt "">and ull_prime like '#searchPRIME#%'</cfif>
> <cfif isDefined("searchCTR") and searchCTR gt
> "">and ull_center like '#searchCTR#%'</cfif>
> <cfif isDefined("searchSBU") and searchSBU gt
> "">and ull_sbu like '#searchSBU#%'</cfif>
> <cfif isDefined("searchPROD") and searchPROD gt
> "">and ull_product like '#searchPROD#%'</cfif>
> <cfif isDefined("searchMIN") and searchMIN gt
> "">and ull_minor like '#searchMIN#%'</cfif>
> <cfif isDefined("searchCUSTOMER") and
> searchCUSTOMER gt "">and ull_customer like '#searchCUSTOMER#%'</cfif>
> <cfif isDefined("searchMISC1") and searchMISC1
> gt "">and ull_misc1 like '#searchMISC1#%'</cfif>
> <cfif isDefined("searchMISC2") and searchMISC2
> gt "">and ull_misc2 like '#searchMISC2#%'</cfif>
> </cfif>
> </cfquery>
>
> How do I convert that to a stored procedure? I am about to give up!
>
> Thanks for you help, Jeff
> Notice. This message is intended only for use by the person or
> entity to which it is addressed. Because it may contain confidential
> information intended solely for the addressee, you are notified that
> any disclosing, copying, downloading, distributing or retaining of
> this message, and any attached files, is prohibited and may be a
> violation of state or federal law. If you received this message in
> error, please notify the sender by reply email, and delete the
> message and all attached files. Thank you.
> _____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
