the simplest example would be
CREATE PROCEDURE getRecords
AS
SELECT * FROM TableOne
SELECT * FROM TableTwo
At 01:50 PM 1/9/01 -0500, you wrote:
>Regarding simple examples of stored procedures OT --
>what should a very simple stored procedure that returns multiple resultsets
>look like?
>
>Lisa
>-----Original Message-----
>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
>Sent: Tuesday, January 09, 2001 12:36 PM
>To: CF-Talk
>Subject: RE: OT SQL STORED PROCEDURES
>
>
>Here's an example script I was given that show how you can loop through a
>query using a cursor. Hope it helps.
>
>Rick
>
>
>USE Master
>GO
>
>DECLARE @DBname VarChar(50)
>
>DECLARE DBCursor SCROLL CURSOR
> FOR SELECT Name FROM Master..SysDatabases
> ORDER BY Name
> -- ORDER BY DBID -- Sys DBIDs: 1 - 6 ; User DBIDs >=
>7
>OPEN DBCursor
> FETCH NEXT FROM DBCursor INTO @DBName
> WHILE (@@FETCH_STATUS = 0) -- 0:fetch successful -1:fetch
>failed -2:row mia
> BEGIN
> PRINT @DBname
> -- do stuff
> FETCH NEXT FROM DBCursor INTO @DBName
> END
>CLOSE DBCursor
>DEALLOCATE DBCursor
>
>This one adds a little bit of Dynamic SQL, which I often find it necessary
>to use.
>
>/* MinimalCursor+DynoSQL.sql */
>/* JWM 200011013 */
>
>USE Master
>GO
>
>DECLARE @DBname VarChar(50)
>DECLARE @SQLString VarChar(255)
>
>SET NOCOUNT ON
>
>DECLARE DBCursor SCROLL CURSOR
> FOR SELECT Name FROM Master..SysDatabases
> ORDER BY DBID
> -- ORDER BY DBID -- Sys DBIDs ~= 1 - 6 ; User DBIDs
>~>= 7
> IF EXISTS (
> SELECT * FROM Information_Schema.Tables
> WHERE Table_Name = 'tmpUserTable'
> )
> DROP TABLE tmpUserTable
> CREATE TABLE tmpUserTable ( Users VarChar(50) )
>OPEN DBCursor
> FETCH NEXT FROM DBCursor INTO @DBName
> WHILE (@@FETCH_STATUS = 0) -- 0:fetch successful -1:fetch
>failed -2:row mia
> BEGIN
> TRUNCATE TABLE tmpUserTable
> PRINT '*** ' + @DBname + ' Database ***'
> -- do stuff
> SET @SQLString = ' INSERT INTO tmpUserTable SELECT ' +
>'[Name]' +
> ' FROM ' + @DBName + '..SysUsers'
> EXEC (@SQLString)
> PRINT @SQLString
> SELECT * FROM tmpUserTable
> FETCH NEXT FROM DBCursor INTO @DBName
> END
>CLOSE DBCursor
>DEALLOCATE DBCursor
>
>SET NOCOUNT OFF
>
>-----Original Message-----
>From: William J Wheatley [mailto:[EMAIL PROTECTED]]
>Sent: Wednesday, January 24, 2001 12:20 PM
>To: CF-Talk
>Subject: OT SQL STORED PROCEDURES
>
>
>WITH THE IF/ELSE/ on Stored Procedures is there a way to write a storedproc
>so it was like this
>
>
>SELECT *
>from member
>where membernumber <>0
>
>IF @name is "0"
>and name='blah'
>end
>if @this eq "that"
>and that='test'
>end
>
>to make it only run certain things if certain values are set
>
>
>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
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
