Re: OT SQL STORED PROCEDURES
What do you mean by OT? I know that on MS SQL Server, it does not allow you to break up a sql statement like that. Making the assumption that the rule applies and is the same on other db's. you would have to do it like this: create proc test1 @groupid int if @groupid = 0 begin select * from groups end ELSE begin select * from groups where groupid = @groupid end GOOD LUCK --- William J Wheatley [EMAIL PROTECTED] wrote: 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
RE: OT SQL STORED PROCEDURES
Something like this: Declare @Name varchar() Declare @This varchar() SELECT @Name = Name,@This = This from member where membernumber 0 IF @name is 'blah' Begin Do Something here end if @this eq "that" begin Do something end -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
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
RE: OT SQL STORED PROCEDURES
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
