Not every query or block of code is a good choice for a stored procedure.
Stored procedures are only ONE reason to move off of access. The first query
is not working because of a datatype mismatch but it's not obvious where it
resides from just looking at the query (which looks fine). As for the second
query you will simply have to change your approach and predetermine the LIKE
clause prior to passing it into the query. As in:
<cfscript>
IF(Isdefined('searchCO'))
likeType = 'searchCo';
IF(IsDefined('searchPrime'))
likeType = 'searchPrime';
</cfscript>
...and so on. You would pass the variable "likeType" into the Stored proc
and use it in a case statement - just like the first query. Obviously there
are many other ways of doing this.
Personally, if it were me - I would not use a stored proc. Indeed I would
use the code below - adding CFQUERYPARAM for speed and security.
-Mark
-----Original Message-----
From: Pratte, Jeff [mailto:[EMAIL PROTECTED]
Sent: Friday, October 08, 2004 1: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]
