Did this help you? Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 16, 2002 8:27 AM To: CF-Talk Subject: RE: Stored Proc Problems i just ran a test on my system and it ran fine. iam using SQL 7.0. i created a view from a table i have in a database and then did a insert/select and it worked fine. i did forget to do one thing in the code i gave you. i forgot to explicitly define the columns to insert into. here is the re-written code. CREATE PROCEDURE sp_missingcursor as CREATE TABLE #TempTable ( [rr] [char] (2) NULL , [memmonth] [varchar] (20) NULL , [memyear] [char] (1) NULL , [intcost1] [numeric](18, 0) NULL , [extcost1] [numeric](18, 0) NULL ) ON [PRIMARY] -- you have to put the column names insert into #tempTable (rr, memmonth, memyear, intcost1, extcost1) SELECT region, memomonth, memoyear, SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST, SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST FROM dbo.CTMCORPAPP WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10', '22') and [desc] Like '%Missing%' GROUP BY memoyear, memomonth, region SELECT * FROM #TempTable DROP TABLE #TempTable See if it works now. Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -----Original Message----- From: Joshua Tipton [mailto:[EMAIL PROTECTED]] Sent: Monday, April 15, 2002 3:57 PM To: CF-Talk Subject: RE: Stored Proc Problems I ran it in query analyzer and views cannot pull data from temp tables was the error given -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, April 15, 2002 3:46 PM To: CF-Talk Subject: RE: Stored Proc Problems shouldn't give you an error. can you post it? Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -----Original Message----- From: Joshua Tipton [mailto:[EMAIL PROTECTED]] Sent: Monday, April 15, 2002 3:47 PM To: CF-Talk Subject: RE: Stored Proc Problems Also my other problem is that I have to pull data from that temp table via a view so when I try a select from #temptable on a dynamic view it gives me an error -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, April 15, 2002 3:27 PM To: CF-Talk Subject: RE: Stored Proc Problems not to impose, but since your looking to see if the table exists and then dropping it, why don't you just use a temp table for the stored procdure. Also, i don't think you will need to use a cursor in this stored procedure if you use the insert/select combination. Rewritten ========= CREATE PROCEDURE sp_missingcursor as CREATE TABLE #TempTable ( [rr] [char] (2) NULL , [memmonth] [varchar] (20) NULL , [memyear] [char] (1) NULL , [intcost1] [numeric](18, 0) NULL , [extcost1] [numeric](18, 0) NULL ) ON [PRIMARY] insert into #tempTable SELECT region, memomonth, memoyear, SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST, SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST FROM dbo.CTMCORPAPP WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10', '22') and [desc] Like '%Missing%' GROUP BY memoyear, memomonth, region SELECT * FROM #TempTable DROP TABLE #TempTable Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -----Original Message----- From: Joshua Tipton [mailto:[EMAIL PROTECTED]] Sent: Monday, April 15, 2002 3:22 PM To: CF-Talk Subject: RE: Stored Proc Problems Actually it is not the insert statements. SQL is using @cfid literally instead of as a variable. Does anyone know how to do this? Joshua Tipton -----Original Message----- From: Joshua Tipton [mailto:[EMAIL PROTECTED]] Sent: Monday, April 15, 2002 2:58 PM To: CF-Talk Subject: Stored Proc Problems I am receiving an error that column name or number of supplied values does not match table definition. Something is up with my insert statements or stored procs do not like dynamic table names. Please help I know this is OT CREATE PROCEDURE sp_missingcursor @cfid nvarchar(50) as if exists (select * from sysobjects where id = object_id(N'[dbo].[@cfid]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[@cfid] CREATE TABLE [dbo].[@cfid] ( [rr] [char] (2) NULL , [memmonth] [varchar] (20) NULL , [memyear] [char] (1) NULL , [intcost1] [numeric](18, 0) NULL , [extcost1] [numeric](18, 0) NULL ) ON [PRIMARY] set nocount on declare @rr char(2), @memmonth nvarchar(20), @memyear char(1), @intcost1 numeric, @extcost1 numeric declare dispo_cursor CURSOR for SELECT region, memomonth, memoyear, SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST, SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST FROM dbo.CTMCORPAPP WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10', '22') and [desc] Like '%Missing%' GROUP BY memoyear, memomonth, region open dispo_cursor fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1, @extcost1 insert into [dbo].[@cfid] select @rr, @memmonth, @memyear, @intcost1, @extcost1 while @@fetch_status = 0 begin fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1, @extcost1 insert into [dbo].[@cfid] select @rr, @memmonth, @memyear, @intcost1, @extcost1 end close dispo_cursor deallocate dispo_cursor ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
