RE: SQL stored procedure question

[Gaulin, Mark]

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]

---