Solved

CF & STORED PROCEDURE QUESTION

Posted on 2003-10-22
19
387 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

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…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now