Posted by Terry Kreft on 11/29/05 11:07

The only way to do that is to create a string anf exec it.

CREATE PROCEDURE udp_End_of_Hour_Estimate
@TableName VarChar
AS

DECLARE @sql varchar(2000)


SET @sql = '
DECLARE @CurrentTime DateTime

SET @CurrentTime = GetDate()

SELECT
(SELECT
Sum(Value)* DatePart(mi,@CurrentTime)/60 AS Emissons
FROM ' +

@TableName
+ '
WHERE
Time BETWEEN
DateAdd(mi,-DatePart(mi,@CurrentTime),@CurrentTime)
AND
@CurrentTime)
+

(SELECT
Avg(Value)* (60-DatePart(mi,@CurrentTime))/60 AS Emissions
FROM ' +
@TableName
+ '
WHERE
Time BETWEEN
DateAdd(mi,-10,@CurrentTime)
AND
@CurrentTime)
'

EXEC (@sql)


--
Terry Kreft



"Steve" <sjc4914@yahoo.com> wrote in message
news:iDOif.2063$4o7.1617@newssvr24.news.prodigy.net...
>I am trying to develop a stored procedure for an existing application that
> has data stored in numerous tables, each with the same set of columns. The
> main columns are Time and Value. There are literally hundreds of these
> tables that are storing values at one minute intervals. I need to
> calculate
> the value at the end of the current hour for any table. I am a little new
> to SQL Server, but I have some experience with other RDBMS.
>
> I get an error from SQL Server, complaining about needing to declare
> @TableName in the body of the procedure. Is there a better way to
> reference
> a table?
>
> Steve
>
> Here is the SQL for creating the procedure:
>
> IF EXISTS(
> SELECT
> ROUTINE_NAME
> FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE
> ROUTINE_TYPE='PROCEDURE'
> AND
> ROUTINE_NAME='udp_End_of_Hour_Estimate')
>
> DROP PROCEDURE udp_End_of_Hour_Estimate
>
> GO
>
> CREATE PROCEDURE udp_End_of_Hour_Estimate
> @TableName VarChar
> AS
>
> DECLARE @CurrentTime DateTime
> SET @CurrentTime = GetDate()
>
> SELECT
> (SELECT
> Sum(Value)* DatePart(mi,@CurrentTime)/60 AS Emissons
> FROM
> @TableName
> WHERE
> Time BETWEEN
> DateAdd(mi,-DatePart(mi,@CurrentTime),@CurrentTime)
> AND
> @CurrentTime)
> +
>
> (SELECT
> Avg(Value)* (60-DatePart(mi,@CurrentTime))/60 AS Emissions
> FROM
> @TableName
> WHERE
> Time BETWEEN
> DateAdd(mi,-10,@CurrentTime)
> AND
> @CurrentTime)
>

• Next in forum: Temp Table and SP Advise
• Prev in forum: Re: Subtracting two columns from diff tables
• Thread view: Re: STORED PROCEDURE - passing table name as a parameter

[Reply to this message]