Posted by Steve on 11/29/05 04:00

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]