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
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.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
