|
|
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)
>
[Back to original message]
|