Dynamic SQL Help Again

[christine1]

I want to simply use a table name as a variable
i.e. Table_Name = cube_unit_@_events_scp

Where the @ sign is it could be one of three options (for table)Do I put the tableName variable here or the report type variable?

What I want to say is if this is the report type then use this table, if its this type Then use this table. Can anyone please help, I've been messing around withthis code more a minute now with no success. See code below (I am concerned with where in the statement this stuff goes)

CREATE PROCEDURE sp_cube_et_sched_maintenance
@design design,
@begin_date datetime,
@phys_piece phys_piece,
@report_type varchar (7),
@table_name varchar (32)
AS

SET nocount ON

CREATE TABLE #tmp_et
(site_phys_piece varchar(12),
aaa_code varchar(12),
inspection_code varchar(3) NULL,
description varchar(60),
site_hrs float NULL,
site_cnt int NULL,
site_avg_hrs float NULL,
xfleet_hrs float NULL,
xfleet_cnt int NULL,
xfleet_avg_hrs float NULL)

CREATE TABLE #tmp_et_site
(site_phys_piece varchar(12),
aaa_code varchar(12),
inspection_code varchar(3) NULL,
description varchar(60),
site_hrs float NULL,
site_cnt int NULL,
site_avg_hrs float NULL)


TRUNCATE TABLE #tmp_et
TRUNCATE TABLE #tmp_et_site

DECLARE tables_cursor CURSOR FOR
SELECT name FROM sysobjects WHERE type = 'U'
AND name like 'cube_unit_'@table_name'_events_scp

OPEN tables_cursor

FETCH NEXT FROM tables_cursor INTO
@tablename

WHILE @@fetch_status <> -1

BEGIN

SELECT @report_type = 'SELECT &quot;' + @table_name + '&quot;, COUNT(*) FROM ' + @table_name

EXEC (@string2)

fetch next from table_cursor into @string1
END

INSERT INTO #tmp_et (site_phys_piece, ebs_code, inspection_code, description,
xfleet_hrs, xfleet_cnt)
SELECT @phys_piece,
aaa_code,
inspection_code,
description,
SUM(c.outage_hours),
SUM(c.event_count)
FROM cube_unit_'@report_type'_events_scp c,
cube_header_info hi
WHERE hi.design = @design
AND c.phys_piece = hi.unit_phys_piece
AND c.begin_date <= @begin_date
AND DATENAME(year, c.begin_date) = DATENAME(year,@begin_date)
GROUP BY c.description, c.aaa_code, c.INSPECTION_CODE
ORDER by c.ebs_code

DECLARE tables_cursor CURSOR FOR
SELECT name FROM sysobjects WHERE type = 'U'
AND name like 'cube_unit_'@table_name'_events_scp

OPEN tables_cursor

FETCH NEXT FROM tables_cursor INTO
@tablename

WHILE @@fetch_status <> -1

BEGIN

SELECT @report_type = 'SELECT &quot;' + @table_name + '&quot;, FROM ' + @table_name

EXEC (@string2)

fetch next from table_cursor into @string1
END

INSERT INTO #tmp_et_site (site_phys_piece, ebs_code, inspection_code, description,
site_hrs, site_cnt, site_avg_hrs)
SELECT @phys_piece,
ebs_code,
inspection_code,
description,
SUM(c.outage_hours),
SUM(c.event_count),
SUM(c.outage_hours) / SUM(c.event_count)
FROM cube_unit_sched_events_scp c,
cube_header_info hi
WHERE hi.site_phys_piece = @phys_piece
AND hi.design = @design
AND c.phys_piece = hi.unit_phys_piece
AND c.begin_date <= @begin_date
AND DATENAME(year, c.begin_date) = DATENAME(year,@begin_date)
GROUP BY c.description, c.ebs_code, c.INSPECTION_CODE
ORDER by c.ebs_code

UPDATE #tmp_et
SET t1.site_hrs = t2.site_hrs,
t1.site_cnt = t2.site_cnt,
t1.site_avg_hrs = t2.site_avg_hrs
FROM #tmp_et t1,
#tmp_et_site t2
WHERE t1.ebs_code = t2.ebs_code
AND t1.inspection_code = t2.inspection_code


UPDATE #tmp_et
SET t1.site_hrs = t2.site_hrs,
t1.site_cnt = t2.site_cnt,
t1.site_avg_hrs = t2.site_avg_hrs
FROM #tmp_et t1,
#tmp_et_site t2
WHERE t1.ebs_code = t2.ebs_code
AND t1.inspection_code IS NULL
AND t2.inspection_code IS NULL

UPDATE #tmp_et
SET xfleet_hrs = xfleet_hrs - site_hrs,
xfleet_cnt = xfleet_cnt - site_cnt
WHERE site_cnt > 0

UPDATE #tmp_et
SET xfleet_avg_hrs = xfleet_hrs / xfleet_cnt
WHERE xfleet_cnt > 0

UPDATE #tmp_et
SET site_hrs = 0.0,
site_cnt = 0
WHERE site_hrs IS NULL

SELECT * from #tmp_et_site
SELECT t.site_phys_piece,
t.ebs_code,
t.inspection_code,
t.description,
t.site_hrs, t.site_cnt, t.site_avg_hrs,
t.xfleet_hrs, t.xfleet_cnt, t.xfleet_avg_hrs
FROM #tmp_et t,
abb_systems a
WHERE a.major_system = SUBSTRING(t.ebs_code, 1, 2)
AND a.design = @design
ORDER BY a.sort_order,
t.site_hrs desc,
t.site_cnt desc,
t.xfleet_hrs desc,
t.xfleet_cnt desc
GO

Thanks,

Christine1