Dynamic SQL Help

[christine1]

Hi everyone,

I am trying to improve one of my sproc's instead up having 3 sproc's that do the same thing, just call FROM 3 different tables. I want to use 1 sproc that utilizes Dynamic SQL to to do the same thing using the variable. Here's my code can anyone tel me where I'm going wrong and please help me with the sytax forthis thing...

--First I create a temp table...

CREATE TABLE #tmp_et
(site_id_piece varchar(12),
site_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_id_piece varchar(12),
site_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

***/SELECT @command_str = 'cube_unit_sched_events_scp'
SELECT @report_type = 'sched'
EXEC (@command_str)/***

INSERT INTO #tmp_et (site_id_piece, site_code, inspection_code, description,
xfleet_hrs, xfleet_cnt)
SELECT @id_piece,
site_code,
inspection_code,
description,
SUM(c.outage_hours),
SUM(c.event_count)
--FROM cube_unit_'sched'_events_scp c, */This is the table name that will change depending on the required report/*
cube_header_info hi
WHERE hi.design = @design
AND c.id_piece = hi.unit_id_piece
AND c.begin_date <= @begin_date
AND DATENAME(year, c.begin_date) = DATENAME(year,@begin_date)
GROUP BY c.description, c.site_code, c.INSPECTION_CODE
ORDER by c.site_code


***/SELECT @command_str = 'cube_unit_sched_events_scp'
EXEC sp_cube_et_sched_maintenance/***

INSERT INTO #tmp_et_site (site_phys_piece, ebs_code, inspection_code, description,site_hrs, site_cnt, site_avg_hrs)
SELECT @id_piece,
site_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.id_piece = hi.id_piece
AND c.begin_date <= @begin_date
AND DATENAME(year, c.begin_date) = DATENAME(year,@begin_date)
GROUP BY c.description, c.site_code, c.INSPECTION_CODE
ORDER by c.site_code


Please help, what am doing wrong?
[sig][/sig]

 

[jnicho02]

Christine,
Does the question boil down to 'how can I use a variable to specify a table name in the FROM clause?' ?

http://www.geocities.com/jnicho02/

 

[christine1]

Yes. I guess I did offer more than I actually needed to. But the scenario is this,

Depending on the report, it may need one of three tables. Never all the tables at once. i.e. The apple report needs to pull from the apple table, red report from red table. I wanted to use dynamic SQL rather than three seperate stored procedures.

I knowe this can be done and rather easily however, I continue to frustrate myself with syntax errors.

Your help is greatly appreciated.

 

[TomSark]

What's up with the ***/ /*** comments?

The selects start with an invalid comment block. Comment blocks in sql server start with /* and end with */

Also, you've commented out part of the line in the FROM clause, but not it's remained on the next line in both selects...

These are definitely causing syntax errors...

Tom

 

[christine1]

Okay, Tom In my haste to post this problem I'm having I crossed the comment block ***/ instead of /*** If my statement is longer than a line I use 3 asterisk, but that's not my issue. I have only commented them out for this posting. Again the question is HOW TO WRITE a table reference as a variable, can this even be done? The -- next to the FROM statement is to draw your attention to where in the statement I want to reference the tables.

I admit I am fairly new at this so your constructive, I emphasize constructive critism is greatly appreciated.

 

[TomSark]

The only way to reference any part of an object reference in a variable such as in server.database.owner.@myobject is to use the EXECUTE statement...

Tom

And take me with a grain of salt, I'm not trying to be difficult, I just come across that way LOL

Tom

 

[christine1]

Thanks Tom.

Now where in the statement do I enter the EXEC command and where do I address the variable (see statement above)?

Christine