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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
