I still think you are better off using IF statements in SQL to build up your SQL string and then Execute it using EXEC(SQLstring)...gives you much more control in my opinion -----Original Message----- From: Eberhart, Robert D [mailto:[EMAIL PROTECTED]] Sent: 17 August 2000 13:35 To: '[EMAIL PROTECTED]' Subject: RE: Noodling over Switch Statements and Stored Procedures and Eff iciency This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C00847.9561F8E0 Content-Type: text/plain; charset="iso-8859-1" I would agree with you ordinarily, but the only problem is that SQL doesn't really allow for Switch/Case statements except within the select statement itself. This means that you have to resort to IFs which is a little bit more inefficient. My idea was to literally just create the text of the SQL statement in CF, pass the text of the SQL through to the stored proc but allow SQL server to actually execute the SQL and do the remaining processing. Sincerely, Robert Eberhart -----Original Message----- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 17, 2000 8:26 AM To: '[EMAIL PROTECTED]' Subject: RE: Noodling over Switch Statements and Stored Procedures and Eff icie ncy Personaly I would dynamically create the SQL statement in a SQL SP rather than CF. The main reason I'd do this is because SQL will give you far more flexibility than CF. I have no idea if there are any performance issues -----Original Message----- From: Eberhart, Robert D [mailto:[EMAIL PROTECTED]] Sent: 17 August 2000 13:21 To: [EMAIL PROTECTED] Subject: Noodling over Switch Statements and Stored Procedures and Efficie ncy This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C00845.929A8660 Content-Type: text/plain; charset="iso-8859-1" I'm just trying to gauge whether this will work or not and still be efficient. We have a simple search form that allows users to search through a database of items. The form gives the users a series of options from which they can pick and thus refine their searches. Now, the stored procedure that provides the engine of this search has a bunch of conditional logic that leads to a different SQL statement based on the options that the user has picked. I wanted to refine this a little bit and make it a bit more efficient. My idea was to dynamically generate a SQL statement in CF using a series of nested switch statements, which to my knowledge are a bit more efficient than CFIF, and then pass the query string into the stored procedure for execution and allow the stored procedure to perform the remaining bit of processing needed on the retrieved information. Is this a valid line of thinking, or should I just leave well enough alone? Sincerely, Robert Eberhart ------_=_NextPart_001_01C00845.929A8660 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2652.35"> <TITLE>Noodling over Switch Statements and Stored Procedures and = Efficiency</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>I'm just trying to gauge whether this will work or = not and still be efficient. We have a simple search form that = allows users to search through a database of items. The form = gives the users a series of options from which they can pick and thus = refine their searches. Now, the stored procedure that provides = the engine of this search has a bunch of conditional logic that leads = to a different SQL statement based on the options that the user has = picked. </FONT></P> <P><FONT SIZE=3D2>I wanted to refine this a little bit and make it a = bit more efficient. My idea was to dynamically generate a SQL = statement in CF using a series of nested switch statements, which to my = knowledge are a bit more efficient than CFIF, and then pass the query = string into the stored procedure for execution and allow the stored = procedure to perform the remaining bit of processing needed on the = retrieved information. Is this a valid line of thinking, or = should I just leave well enough alone?</FONT></P> <P><FONT SIZE=3D2>Sincerely,</FONT> <BR><FONT SIZE=3D2>Robert Eberhart</FONT> </P> </BODY> </HTML> ------_=_NextPart_001_01C00845.929A8660-- ---------------------------------------------------------------------------- -- Archives: http://www.mail-archive.com/[email protected]/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. ---------------------------------------------------------------------------- -- Archives: http://www.mail-archive.com/[email protected]/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. ------_=_NextPart_001_01C00847.9561F8E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2652.35"> <TITLE>RE: Noodling over Switch Statements and Stored Procedures and = Efficiency</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>I would agree with you ordinarily, but the only = problem is that SQL doesn't really allow for Switch/Case statements = except within the select statement itself. This means that you = have to resort to IFs which is a little bit more inefficient. My = idea was to literally just create the text of the SQL statement in CF, = pass the text of the SQL through to the stored proc but allow SQL = server to actually execute the SQL and do the remaining = processing.</FONT></P> <P><FONT SIZE=3D2>Sincerely,</FONT> <BR><FONT SIZE=3D2>Robert Eberhart</FONT> </P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: Andy Ewings [<A = HREF=3D"mailto:[EMAIL PROTECTED]">mailto:[EMAIL PROTECTED]= uk</A>]</FONT> <BR><FONT SIZE=3D2>Sent: Thursday, August 17, 2000 8:26 AM</FONT> <BR><FONT SIZE=3D2>To: '[EMAIL PROTECTED]'</FONT> <BR><FONT SIZE=3D2>Subject: RE: Noodling over Switch Statements and = Stored Procedures and</FONT> <BR><FONT SIZE=3D2>Eff icie ncy</FONT> </P> <BR> <P><FONT SIZE=3D2>Personaly I would dynamically create the SQL = statement in a SQL SP rather</FONT> <BR><FONT SIZE=3D2>than CF. The main reason I'd do this is = because SQL will give you far more</FONT> <BR><FONT SIZE=3D2>flexibility than CF. I have no idea if there = are any performance issues</FONT> </P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: Eberhart, Robert D [<A = HREF=3D"mailto:[EMAIL PROTECTED]">mailto:robert.eberhart@ba= esystems.com</A>]</FONT> <BR><FONT SIZE=3D2>Sent: 17 August 2000 13:21</FONT> <BR><FONT SIZE=3D2>To: [EMAIL PROTECTED]</FONT> <BR><FONT SIZE=3D2>Subject: Noodling over Switch Statements and Stored = Procedures and</FONT> <BR><FONT SIZE=3D2>Efficie ncy</FONT> </P> <BR> <P><FONT SIZE=3D2>This message is in MIME format. Since your mail = reader does not understand</FONT> <BR><FONT SIZE=3D2>this format, some or all of this message may not be = legible.</FONT> </P> <P><FONT SIZE=3D2>------_=3D_NextPart_001_01C00845.929A8660</FONT> <BR><FONT SIZE=3D2>Content-Type: text/plain;</FONT> <BR> <FONT = SIZE=3D2>charset=3D"iso-8859-1"</FONT> </P> <P><FONT SIZE=3D2>I'm just trying to gauge whether this will work or = not and still be</FONT> <BR><FONT SIZE=3D2>efficient. We have a simple search form that = allows users to search through</FONT> <BR><FONT SIZE=3D2>a database of items. The form gives the users = a series of options from</FONT> <BR><FONT SIZE=3D2>which they can pick and thus refine their = searches. Now, the stored</FONT> <BR><FONT SIZE=3D2>procedure that provides the engine of this search = has a bunch of conditional</FONT> <BR><FONT SIZE=3D2>logic that leads to a different SQL statement based = on the options that the</FONT> <BR><FONT SIZE=3D2>user has picked. </FONT> </P> <P><FONT SIZE=3D2>I wanted to refine this a little bit and make it a = bit more efficient. My</FONT> <BR><FONT SIZE=3D2>idea was to dynamically generate a SQL statement in = CF using a series of</FONT> <BR><FONT SIZE=3D2>nested switch statements, which to my knowledge are = a bit more efficient</FONT> <BR><FONT SIZE=3D2>than CFIF, and then pass the query string into the = stored procedure for</FONT> <BR><FONT SIZE=3D2>execution and allow the stored procedure to perform = the remaining bit of</FONT> <BR><FONT SIZE=3D2>processing needed on the retrieved = information. Is this a valid line of</FONT> <BR><FONT SIZE=3D2>thinking, or should I just leave well enough alone?</= FONT> </P> <P><FONT SIZE=3D2>Sincerely,</FONT> <BR><FONT SIZE=3D2>Robert Eberhart</FONT> </P> <P><FONT SIZE=3D2>------_=3D_NextPart_001_01C00845.929A8660</FONT> <BR><FONT SIZE=3D2>Content-Type: text/html;</FONT> <BR> <FONT = SIZE=3D2>charset=3D"iso-8859-1"</FONT> <BR><FONT SIZE=3D2>Content-Transfer-Encoding: quoted-printable</FONT> </P> <P><FONT SIZE=3D2><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML = 3.2//EN"></FONT> <BR><FONT SIZE=3D2><HTML></FONT> <BR><FONT SIZE=3D2><HEAD></FONT> <BR><FONT SIZE=3D2><META HTTP-EQUIV=3D3D"Content-Type" = CONTENT=3D3D"text/html; =3D</FONT> <BR><FONT SIZE=3D2>charset=3D3Diso-8859-1"></FONT> <BR><FONT SIZE=3D2><META NAME=3D3D"Generator" = CONTENT=3D3D"MS Exchange Server version =3D</FONT> <BR><FONT SIZE=3D2>5.5.2652.35"></FONT> <BR><FONT SIZE=3D2><TITLE>Noodling over Switch Statements and = Stored Procedures and =3D</FONT> <BR><FONT SIZE=3D2>Efficiency</TITLE></FONT> <BR><FONT SIZE=3D2></HEAD></FONT> <BR><FONT SIZE=3D2><BODY></FONT> </P> <P><FONT SIZE=3D2><P><FONT SIZE=3D3D2>I'm just trying to = gauge whether this will work or =3D</FONT> <BR><FONT SIZE=3D2>not and still be efficient.&nbsp; We have a = simple search form that =3D</FONT> <BR><FONT SIZE=3D2>allows users to search through a database of = items.&nbsp; The form =3D</FONT> <BR><FONT SIZE=3D2>gives the users a series of options from which they = can pick and thus =3D</FONT> <BR><FONT SIZE=3D2>refine their searches.&nbsp; Now, the stored = procedure that provides =3D</FONT> <BR><FONT SIZE=3D2>the engine of this search has a bunch of conditional = logic that leads =3D</FONT> <BR><FONT SIZE=3D2>to a different SQL statement based on the options = that the user has =3D</FONT> <BR><FONT SIZE=3D2>picked.&nbsp; </FONT></P></FONT> </P> <P><FONT SIZE=3D2><P><FONT SIZE=3D3D2>I wanted to refine = this a little bit and make it a =3D</FONT> <BR><FONT SIZE=3D2>bit more efficient.&nbsp; My idea was to = dynamically generate a SQL =3D</FONT> <BR><FONT SIZE=3D2>statement in CF using a series of nested switch = statements, which to my =3D</FONT> <BR><FONT SIZE=3D2>knowledge are a bit more efficient than CFIF, and = then pass the query =3D</FONT> <BR><FONT SIZE=3D2>string into the stored procedure for execution and = allow the stored =3D</FONT> <BR><FONT SIZE=3D2>procedure to perform the remaining bit of processing = needed on the =3D</FONT> <BR><FONT SIZE=3D2>retrieved information.&nbsp; Is this a valid = line of thinking, or =3D</FONT> <BR><FONT SIZE=3D2>should I just leave well enough = alone?</FONT></P></FONT> </P> <P><FONT SIZE=3D2><P><FONT = SIZE=3D3D2>Sincerely,</FONT></FONT> <BR><FONT SIZE=3D2><BR><FONT SIZE=3D3D2>Robert = Eberhart</FONT></FONT> <BR><FONT SIZE=3D2></P></FONT> </P> <P><FONT SIZE=3D2></BODY></FONT> <BR><FONT SIZE=3D2></HTML></FONT> <BR><FONT SIZE=3D2>------_=3D_NextPart_001_01C00845.929A8660--</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -------------</FONT> <BR><FONT SIZE=3D2>--</FONT> <BR><FONT SIZE=3D2>Archives: <A = HREF=3D"http://www.mail-archive.com/[email protected]/" = TARGET=3D"_blank">http://www.mail-archive.com/[email protected]/= </A></FONT> <BR><FONT SIZE=3D2>To Unsubscribe visit</FONT> <BR><FONT SIZE=3D2><A = HREF=3D"http://www.houseoffusion.com/index.cfm?sidebar=3Dlists&body=3Dli= sts/cf_talk" = TARGET=3D"_blank">http://www.houseoffusion.com/index.cfm?sidebar=3Dlists= &body=3Dlists/cf_talk</A> or</FONT> <BR><FONT SIZE=3D2>send a message to [EMAIL PROTECTED] = with 'unsubscribe' in</FONT> <BR><FONT SIZE=3D2>the body.</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= ---------------</FONT> <BR><FONT SIZE=3D2>Archives: <A = HREF=3D"http://www.mail-archive.com/[email protected]/" = TARGET=3D"_blank">http://www.mail-archive.com/[email protected]/= </A></FONT> <BR><FONT SIZE=3D2>To Unsubscribe visit <A = HREF=3D"http://www.houseoffusion.com/index.cfm?sidebar=3Dlists&body=3Dli= sts/cf_talk" = TARGET=3D"_blank">http://www.houseoffusion.com/index.cfm?sidebar=3Dlists= &body=3Dlists/cf_talk</A> or send a message to = [EMAIL PROTECTED] with 'unsubscribe' in the = body.</FONT></P> </BODY> </HTML> ------_=_NextPart_001_01C00847.9561F8E0-- ---------------------------------------------------------------------------- -- Archives: http://www.mail-archive.com/[email protected]/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. ------------------------------------------------------------------------------ Archives: http://www.mail-archive.com/[email protected]/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
