CF & STORED PROCEDURE QUESTION

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
........................................................................
mappenzellarAsked:
Who is Participating?
 
mrmcfeelyConnect With a Mentor Commented:
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
 
mrmcfeelyCommented:
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
 
hartCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
tuttiwalaCommented:
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
 
mappenzellarAuthor Commented:
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
 
Tacobell777Commented:
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
 
tuttiwalaCommented:
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
 
mappenzellarAuthor Commented:
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
 
tuttiwalaCommented:
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
 
mrmcfeelyCommented:
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
 
mappenzellarAuthor Commented:
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
 
mappenzellarAuthor Commented:
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
 
mrmcfeelyCommented:
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
 
mappenzellarAuthor Commented:
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
 
mappenzellarAuthor Commented:
actually it works, I just overlooked something
0
 
mrmcfeelyCommented:
Great!  Is it performing better?
0
 
mappenzellarAuthor Commented:
Seems to be a little better. Not great, but better.
0
 
mrmcfeelyCommented:
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
 
mappenzellarAuthor Commented:
actually already did. thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.