(faster,more stable) than Access even if you don't use stored procedures for
everything."
I would have to disagree, MS SQL Server has its limitations in Programming
Stored Procedures but the things
you point out can easily be done is SP's.
You are using cfQuery to dynamically build an SQL Command, the same can be
done with SP's elegantly
using 2 contructs.
1. Dynamic SQL Build
2. Parameter driven SQL
Stored Procedures are a clean way of abstracting your data calls to the DB,
especially in instances
where you have to have wired calls for Multiple DB transactions.
Now, it would be nice if SQL Server provided a set of Java API's to access
Stored Procedures
as Java Classes and accessible kinda like Prepared Statements work.
cfQuery is just an easy dirty way of getting the job done, once you start
getting to complex
application development... cfQuery and SQL all over the application is
Messy.
Joe Eugene
-----Original Message-----
From: Gaulin, Mark [mailto:[EMAIL PROTECTED]
Sent: Friday, October 08, 2004 3:50 PM
To: CF-Talk
Subject: RE: SQL stored procedure question
Stored procedures don't work the way you are expecting them to work, and
you are discovering. When you're using cfquery you can use cf variables and
cf logic to build a sql statement, and when the statement is all built it
goes to the sql server (or access, or whatever) and then it's run. That
means you can do pretty much anything in the cfquery tag to build you sql
statement, as long as the final output is valid sql. In effect, you're
using cf to write a program in another language (sql), and then running it.
This is one of the coolest features of cf because it is really powerful (as
your original cfquery code proves).
When you write a stored procedures the entire procedure must be valid sql
*before* you apply any of the "if" logic and variables replacements.
Variable names can only go where constants go, for example, so you could
never have a variable that said "order by xyz" and just stick it on the end
of a select. (There is a way around that, using exec, but it's a hassle.)
sql is not a tremendously flexible language and some things are just not
easy to do... your second query is a good example of something that's a real
pain.
I'd suggest sticking with cfquery for now. SQL Server is going to better
(faster,more stable) than Access even if you don't use stored procedures for
everything.
Mark
-----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]
