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
