Solved

CF & STORED PROCEDURE QUESTION

Posted on 2003-10-22
19
389 Views
Last Modified: 2013-12-24
Ok, I have a query shown below. I want to turn it into a stored procedure, my question is, how/can I do the cfif statements with a stored procedure?

........................................................................
QUERY:

SELECT      start_date,      adid,end_date,camp_views,image_name,alt_text,views
FROM      adsystem
WHERE      section = '#ATTRIBUTES.section#' AND active  = 1 AND
                <CFIF Trim(ATTRIBUTES.adsize) NEQ ''>
            adsize = '#ATTRIBUTES.adsize#' AND
      </CFIF>
      start_date <= #Now()#
      <CFIF IsDefined('CALLER.adlist')>
           AND adid NOT IN (#CALLER.adlist#)
      </CFIF>
      <CFIF IsDefined('ATTRIBUTES.adid')>
           AND adid = #ATTRIBUTES.adid#
      </CFIF>;
........................................................................
STORED PROCEDURE:
CREATE PROCEDURE GET_TYPE
(@section varchar(50), @adsize varchar(50), @adlist int, @adid int)
as

SELECT start_date,adid,end_date,camp_views,image_name,alt_text,views
FROM adsystem
WHERE section = '@section'
AND active  = 1
AND adsize = '@adsize'
AND start_date <= getdate()
AND adid NOT IN (@adlist)
AND adid = @adid
GO
........................................................................
0
Comment
Question by:mappenzellar
  • 8
  • 6
  • 3
  • +2
19 Comments
 
LVL 4

Expert Comment

by:mrmcfeely
ID: 9601870
There's no real graceful way to transform that dynamic SQL into a stored proc (at least not any ways that will give you a good gain in efficiency with MS SQL Server).  That 'NOT IN' operator really screws things up, actually... SQL Server does not support "list" or "collection" parameters (although there are a few hacks you can use to get a similar effect).

Your best bet would be to take advantage of the CFQUERYPARAM tag.  Here's a page that'll show you how it's used and will breifly explain its advantages, in terms of performance and security:
http://tutorial138.easycfm.com/ 

Is there any specific reason you want to use stored procs?
0
 
LVL 11

Expert Comment

by:hart
ID: 9604786
i ahve done a lot of usage of if and else conditions in oracle 9i, but i am not so sure about sql server

Regards
Hart
0
 

Expert Comment

by:tuttiwala
ID: 9623863
Here is what you need:

CREATE PROCEDURE GET_TYPE
 @section varchar(50),
 @adsize varchar(50) = null,
 @adlist varchar(500) = null, --list of adids ex. (1,2,3,4,5)
 @adid int = null
as

DECLARE @sql varchar(1000)
select @sql = 'SELECT start_date,adid,end_date,camp_views,image_name,alt_text,views  FROM adsystem WHERE start_date <= getdate() AND active = 1 AND section = ' + @section

select @adsize = ltrim(rtrim(@adsize))

if @adsize is not null and @adsize <> ''
   select @sql = @sql + ' AND adsize = ' + @adsize

if @adlist is not null
   select @sql = @sql + ' AND adid NOT IN (' + @adlist +')'

if @adid is not null
   select @sql = @sql + ' AND adid = ' + @adid

exec(@sql)
return


--------
that is what you need i believe. let me know if that helps.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:mappenzellar
ID: 9634247
So how would I write the actual procedure...this isn't working:

<CFSTOREDPROC PROCEDURE="get_other" DATASOURCE="test">            
     <CFPROCRESULT NAME = get_other>
          <CFIF Trim(ATTRIBUTES.adsize) NEQ ''>
      <CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#ATTRIBUTES.adsize#" DBVARNAME="@adsize">
          </CFIF>
           <CFIF IsDefined('CALLER.adlist')>
       <CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#CALLER.adlist#" DBVARNAME="@adlist">      
           </CFIF>
           <CFIF IsDefined('ATTRIBUTES.adid')>
      <CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_INT" VALUE="#ATTRIBUTES.adid#" DBVARNAME="@adid">  
           </CFIF>
</CFSTOREDPROC>            
0
 
LVL 17

Expert Comment

by:Tacobell777
ID: 9646720
Don't forget you can;t reference a variable in the stored procedure by name, you need to pass the variables in the order they are declared in the stored procedure.
0
 

Expert Comment

by:tuttiwala
ID: 9647220
mappenzellar,

i was under the impression that you wanted to convert your query into a pure stored procedure (which you would create in SQL Server, I'm assuming you are using SQL Server - what are you using?) and then call the stored procedure from your coldfusion code passing it the variables it needs in the order of declaration (which taco correctly pointed out)

Am i mistaken?

0
 

Author Comment

by:mappenzellar
ID: 9649689
tuttiwala, I don't do a lot with stored procecdures so I apologize. I guess what I am asking is with your solution how should I pass the variables to it when some variables might not exist like in my original query?
0
 

Expert Comment

by:tuttiwala
ID: 9650591
oh ok.

well, if you take a look at my stored procedure where i have declared the input parameters:
CREATE PROCEDURE GET_TYPE
 @section varchar(50),
 @adsize varchar(50) = null,
 @adlist varchar(500) = null, --list of adids ex. (1,2,3,4,5)
 @adid int = null
as

you will see that i have put ' = null '.

This in essence allows this parameter to be optional.

so if you call this stored procedure and only pass it the section parameter, (adsize, adlist, and adid would all be null) and the stored procedure then takes care of this situation.


example:
<CFSTOREDPROC PROCEDURE="get_other" DATASOURCE="test">      
<!--- cfprocresult tags --->
<CFPROCRESULT NAME=get_other>
<!--- cfprocparam tags --->
<CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#ATTRIBUTES.section#" DBVARNAME="@section">
<CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#ATTRIBUTES.adsize#" DBVARNAME="@adsize">
<CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#CALLER.adlist#" DBVARNAME="@adlist">
<CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_INT" VALUE="#ATTRIBUTES.adid#" DBVARNAME="@adid">
</CFSTOREDPROC>  
<!--- Close the cfstoredproc tag --->
</cfstoredproc>

<h3>The Results Information</h3>
<cfoutput>Record Count: #get_other.recordCount#</cfoutput>

I believe that should work for you.
0
 
LVL 4

Expert Comment

by:mrmcfeely
ID: 9650610
mappenzellar,

Is there any specific reason you want to use a stored procedure?  It seems like a stored procedure may not be the best option for what you're trying to do.
0
 

Author Comment

by:mappenzellar
ID: 9650628
I have just read time after time to avoid normal queries and I know this code gets hit a ton which is affecting the performance of our site. Thanks tuttiwala I will test it out!!!!
0
 

Author Comment

by:mappenzellar
ID: 9650681
doesn't appear to be working. It is having a problem with the if statements that need to be included, as shown above, there might for example #ATTRIBUTES.adid# might not always contain something
0
 
LVL 4

Expert Comment

by:mrmcfeely
ID: 9650942
It's true that stored procedures are almost always better than inline queries, but I think you might have one of the few exceptions to that rule here.  The fact is, the WHERE clause in your query is dynamic... it's not just the values that change, but the conditional statements (in their entirety) vary.  The former situation is really what stored procedures are optimized for... the latter situation brings up some problems with stored procedures.

Let's consider what you gain by using stored procedures.  One, you have a nice separation of database logic out of the presentation (ColdFusion) logic.  Two, stored procedures are compiled, and the query optimizer knows what to expect every time, and may even cache execution plans (this is mainly why you get the performance increase).  Unfortunately, you don't get that second benefit if you execute dynamic SQL with the "exec()" function... you'd essentially be building an arbitrary SQL string and executing it (pretty much the same as your plain vanilla CFQUERY), rather than taking a "prepared" (compiled/cached) statement and plugging in some parameters.  It's like the difference between a compiled function and an interpreted piece of script.

There's also a gain in security by using stored procedures (parameters are strongly typed and bound in the SQL statement, making SQL injection attacks impossible).  But again, if you're simply building a SQL string and executing it in the stored procedure, you don't get this advantage.

Fortunately, Coldfusion does give you the means to create "prepared statements" without necessarily having to write stored procedures.  This is where the CFQUERYPARAM tag comes in.  When you use the CFQUERYPARAM tag, it uses parameter binding, which allows the database to "compile" the SQL statement and cache its execution plan if possible.  This means you get close to the same performance gain as a traditional stored procedure (not to mention better security), but you can keep the flexibility you have when you use the CFQUERY tag.  I find the CFQUERY/CFQUERYPARAM approach to be especially useful when the string in the WHERE clause of your SELECT statement is dynamic... not only that, but the CFQUERYPARAM tag can support lists, which is something that stored procedures don't natively support.  The drawback of this approach is that you do not necessarily get the first advantage I mentioned... namely, the separation of database and presentation logic.  Of course, you could always save your query in a separate file and CFINCLUDE or CFMODULE (a la FuseBox) it into your main page.

Here's what your query would look like if you were to use the CFQUERYPARAM tag:

<CFQUERY DATASOURCE="#REQUEST.datasource#" NAME="qryExample">
      SELECT      start_date,    
            adid,
            end_date,
            camp_views,
            image_name,
            alt_text,
            views
      FROM      adsystem
      WHERE      section = <CFQUERYPARAM VALUE="#ATTRIBUTES.section#" CFSQLTYPE="CF_SQL_VARCHAR">
            AND active  = 1
            AND
            <CFIF Trim(ATTRIBUTES.adsize) NEQ ''>
                  adsize = <CFQUERYPARAM VALUE="#ATTRIBUTES.adsize#" CFSQLTYPE="CF_SQL_VARCHAR"> AND
            </CFIF>
            start_date <= <CFQUERYPARAM VALUE="#Now()#" CFSQLTYPE="CF_SQL_TIMESTAMP">
            <CFIF IsDefined('CALLER.adlist')>
                  AND adid NOT IN (<CFQUERYPARAM VALUE="#CALLER.adlist#" LIST="YES" CFSQLTYPE="CF_SQL_INTEGER">)
            </CFIF>
            <CFIF IsDefined('ATTRIBUTES.adid')>
                  AND adid = <CFQUERYPARAM VALUE="#ATTRIBUTES.adid#" CFSQLTYPE="CF_SQL_INTEGER">
            </CFIF>
</CFQUERY>

Turn on debugging and give it a try... compare the query times between the CFQUERYPARAM version and the non-CFQUERYPARAM version and you should be able to clearly see the performance increase!

Also check out that link in my original post if you get a chance... it covers the advantages of CFQUERYPARAM in a little more detail.
0
 

Author Comment

by:mappenzellar
ID: 9651254
Well I know you are gonna think I am nuts, but when I use query params with this query, it doesn't work....I even copied and pasted your example and it didn't work. Here is the breakdown of the field types: Maybe I have something messed up? Works fine when I take out the params. The query is called from cfmodule, would that have anything to do with anything?

[adid] [int] NOT NULL ,
[alt_text] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[image_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[url] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[login] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[camp_views] [int] NULL ,
[start_date] [datetime] NULL ,
[end_date] [datetime] NULL ,
[descrip] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[section] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[active] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[adsize] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[code] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[views] [int] NULL CONSTRAINT [DF_ADSYSTEM_views] DEFAULT (0)


0
 
LVL 4

Accepted Solution

by:
mrmcfeely earned 50 total points
ID: 9651304
What error message are you getting?

You may want to wrap the Now() function with the CreateODBCDateTime() function (assuming that you're using ODBC).  Like this:

          start_date <= <CFQUERYPARAM VALUE="#CreateODBCDateTime(Now())#" CFSQLTYPE="CF_SQL_TIMESTAMP">
0
 

Author Comment

by:mappenzellar
ID: 9651475
actually it works, I just overlooked something
0
 
LVL 4

Expert Comment

by:mrmcfeely
ID: 9651492
Great!  Is it performing better?
0
 

Author Comment

by:mappenzellar
ID: 9651510
Seems to be a little better. Not great, but better.
0
 
LVL 4

Expert Comment

by:mrmcfeely
ID: 9651561
Now that I look at it again, you may even want to change this line:
       AND active  = 1
to:
       AND active  = <CFQUERYPARAM value="1" CFSQLTYPE="CF_SQL_INTEGER">
0
 

Author Comment

by:mappenzellar
ID: 9651883
actually already did. thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Time Sheet Help 6 56
Htaccess - if subdomain "dev." 2 78
AD LDS, AD FS, RODC, LDAP access for 3rd party vendors? 5 113
Help with a redirect in web.config file 8 42
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question