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
