Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

CF & STORED PROCEDURE QUESTION

Posted on 2003-10-22
19
Medium Priority
?
404 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

 

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 200 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

Plug and play, no additional software required!

The ATEN UE3310 USB3.1 Gen1 Extender Cable allows users to extend the distance between the computer and USB devices up to 10 m (33 ft). The UE3310 is a high-quality, cost-effective solution for professional environments such as hospitals, factories and business facilities.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

722 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