ok how would you use SP_execute sql with coldfusion then?
=) and the reason i'm wanting to use STored Procedures is because this takes
about 30 seconds to run via Queries, and joins & temp tables, etc so i want
to make this into a stored Procedure to speed it up. I've noticed just the
start without variables instead of 30 seconds on query it was down to about
8 in a stored proc so if i want to make it so it will variably run SARGS in
the Procedure, now if the doing the @thisandthis stuff is going to cause
performance hits, will i be better off to run it with querys? Are stored
procedures not designed to run this type of dynamic processing?
Thanks
Bill Wheatley
Director of Development
AEPS INC
Allaire ColdFusion Consulting Partner
Allaire Certified ColdFusion Developer
http://www.aeps.com
ICQ: 417645
http://www.aeps2000.com
954-472-6684 X303
IMPORTANT NOTICE:
This e-mail and any attachment to it is intended only to be read or used by
the named addressee. It is confidential and may contain legally privileged
information. No confidentiality or privilege is waived or lost by any
mistaken transmission to you. If you receive this e-mail in error, please
immediately delete it from your system and notify the sender. You must not
disclose, copy or use any part of this e-mail if you are not the intended
recipient. The RTA is not responsible for any unauthorized alterations to
this e-mail or attachment to it
-----Original Message-----
From: Peter Stolz [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 10, 2001 8:20 AM
To: CF-Talk
Subject: RE: OT STORED PROCEDURES
Use sp_executesql instead of EXEC() to run your dynamic SQL. It caches the
query plans.
P.
-----Original Message-----
From: Eric Barr [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 10, 2001 7:59 AM
To: CF-Talk
Subject: RE: OT STORED PROCEDURES
I've gotten one result set back from exec before ...never tired multiple,
but I don't see why not ...its Executing SQL.
BUT, be careful with EXEC ...you can incur some pretty significant
performance hits ..... it messes up the SP plan caching, i.e. loosing one of
the major benefits of SP's ...speed.
------------------------------------------------
Common sense is genius dressed in its working clothes.
-- Ralph Waldo Emerson
Eric Barr
Zeff Design
(p) 212.714.6390
(f) 212.580.7181
-----Original Message-----
From: Joe Sheble (Wizaerd) [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 10, 2001 1:15 AM
To: CF-Talk
Subject: RE: OT STORED PROCEDURES
I had thought of using Dynamic SQL in SQL Server stored procedures, but
hadn't gotten around with playing with it much yet. Can you still return
multiple recordsets this way? In fact, does the EXEC() SQL function return
a recordset at all?
Joseph E. Sheble
a.k.a. Wizaerd
Wizaerd's Realm
Canvas, 3D, Graphics,
ColdFusion, PHP, and mySQL
http://www.wizaerd.com
=================================
> -----Original Message-----
> From: Bob Silverberg [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, January 09, 2001 3:50 PM
> To: CF-Talk
> Subject: RE: OT STORED PROCEDURES
>
>
> I just wrote this off the top of my head, so it may not run (I'm not sure
> about the double and single quotes), but it gives you some idea of how to
> dynamically build SQL statements in a stored proc (if that's what you're
> looking for):
>
> CREATE PROC MyProc
> @Active bit = NULL,
> @Test bit = NULL
> AS
>
> DECLARE @sSQL as varchar(1000)
>
> SET @sSQL = 'Select * from member where membernumber <> 0'
> IF @Active = 1 SET @sSQL = @sSQL + 'and active="y"'
> IF @Test = 1 SET @sSQL = @sSQL + 'and test="no"'
> SET @sSQL = @sSQL + 'order by membernumber'
>
> EXEC (@sSQL)
>
> GO
>
> -----Original Message-----
> From: William J Wheatley [mailto:[EMAIL PROTECTED]]
> Sent: January 9, 2001 3:47 PM
> To: CF-Talk
> Subject: RE: OT STORED PROCEDURES
>
>
> is there anyway to do the if in the query of a stored procedure without
> using a Cursor.
>
> and this is what i wanted
>
> Select *
> from member
> where membernumber<>0
> and active='y'
> and test='no'
> order by membernumber
>
> by using this type logic
>
> select *
> from member
> where membernumber<>0
>
> IF @active is 'y'
> and active='y'
> END
> IF @test is 'y'
> and test='no'
> END
> order by membernumber
>
>
> so that by using a stored procedure i can say is active is N then the (and
> active='y') wont run properly.
> =)
>
> hope that helps
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
