STORED PROCEDURE - passing table name as a parameter

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)

re: STORED PROCEDURE - passing table name as a parameter

On Tue, 29 Nov 2005 02:00:46 GMT, Steve <sjc4914@yahoo.com> wrote:
[color=blue]
>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[/color]

In the relational database realm, this is what we call a RED FLAG. If data
has the same set of columns, it should be in the same table. To identify
distinct sets of data within a table, you add one field. Anything else is
going greatly against the grain of how SQL is designed to work, and limits
what you can do with the data.

Tables are structural elements - rows are data elements. RDBMSs are not
designed to use structural elements as data elements.

re: STORED PROCEDURE - passing table name as a parameter

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.ne t...[color=blue]
>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)
>[/color]

re: STORED PROCEDURE - passing table name as a parameter

Steve wrote:[color=blue]
> 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.
>[/color]

Just to emphasise what Steve Jorgensen said: this sounds like a crazy
design. If you are forced to support hundreds of tables like this then
you might consider creating some indexed views so that you don't have
to implement a lot of dynamic code. Take a look at the indexed views
topic in Books Online.

If on the other hand these hundreds of identical tables are somehow
being created dynamically at runtime then I be much more hesitant about
going further. I don't know your system or what it does of course.
However I'm pretty sure that if confronted with that problem I would
first write to management in the strongest terms that such an
architecture is unsustainable and needs to be terminated rather than
supported.

--
David Portas
SQL Server MVP
--

re: STORED PROCEDURE - passing table name as a parameter

This is a "commercial" database solution purchased to record and report
environmental data. I don't like the design for all the reasons already
posted, and more. Though I had nothing to do with the selection,
unfortunately, I am charged with completing a task, so like it or not, it
is on my plate.
Terry Kreft's idea looks workable, but not optimum. It would seem like there
would be a way to get around pasting strings together and then executing
the string. I was wondering if there was a way to use a subquery to return
a table reference. It is a rather obtuse way of getting there, and I
haven't investigated it yet. Any alternate solutions anyone can think of?

Thanks for the responses,
Steve


David Portas wrote:
[color=blue]
> Steve wrote:[color=green]
>> 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.
>>[/color]
>
> Just to emphasise what Steve Jorgensen said: this sounds like a crazy
> design. If you are forced to support hundreds of tables like this then
> you might consider creating some indexed views so that you don't have
> to implement a lot of dynamic code. Take a look at the indexed views
> topic in Books Online.
>
> If on the other hand these hundreds of identical tables are somehow
> being created dynamically at runtime then I be much more hesitant about
> going further. I don't know your system or what it does of course.
> However I'm pretty sure that if confronted with that problem I would
> first write to management in the strongest terms that such an
> architecture is unsustainable and needs to be terminated rather than
> supported.
>[/color]

re: STORED PROCEDURE - passing table name as a parameter

On Wed, 30 Nov 2005 00:26:33 GMT, Steve <sjc4914@yahoo.com> wrote:
[color=blue]
>This is a "commercial" database solution purchased to record and report
>environmental data. I don't like the design for all the reasons already
>posted, and more. Though I had nothing to do with the selection,
>unfortunately, I am charged with completing a task, so like it or not, it
>is on my plate.[/color]

Sorry - didn't mean to insult your intelligence. It helps to add a disclaimer
about not having control of the design, so people will know to answer what you
ask, not the red flag you can't do anything about.
[color=blue]
>Terry Kreft's idea looks workable, but not optimum. It would seem like there
>would be a way to get around pasting strings together and then executing
>the string. I was wondering if there was a way to use a subquery to return
>a table reference. It is a rather obtuse way of getting there, and I
>haven't investigated it yet. Any alternate solutions anyone can think of?[/color]

A subquery won't help because that's just another query that returns data from
the objects it was written to return data from (and no others). Basically, to
do what you need to do, you have to use some kind if Dynamic SQL which is
another name for "pasting strings together and then executing the string".

re: STORED PROCEDURE - passing table name as a parameter

No insult taken. Oh, well, I appreciate all quick feedback on the issue.
This is really a pretty civil group.

Best regards,
Steve Caldwell


Steve Jorgensen wrote:
[color=blue]
> On Wed, 30 Nov 2005 00:26:33 GMT, Steve <sjc4914@yahoo.com> wrote:
>[color=green]
>>This is a "commercial" database solution purchased to record and report
>>environmental data. I don't like the design for all the reasons already
>>posted, and more. Though I had nothing to do with the selection,
>>unfortunately, I am charged with completing a task, so like it or not, it
>>is on my plate.[/color]
>
> Sorry - didn't mean to insult your intelligence. It helps to add a
> disclaimer about not having control of the design, so people will know to
> answer what you ask, not the red flag you can't do anything about.
>[color=green]
>>Terry Kreft's idea looks workable, but not optimum. It would seem like
>>there would be a way to get around pasting strings together and then
>>executing the string. I was wondering if there was a way to use a subquery
>>to return a table reference. It is a rather obtuse way of getting there,
>>and I haven't investigated it yet. Any alternate solutions anyone can
>>think of?[/color]
>
> A subquery won't help because that's just another query that returns data
> from
> the objects it was written to return data from (and no others).
> Basically, to do what you need to do, you have to use some kind if Dynamic
> SQL which is another name for "pasting strings together and then executing
> the string".
>[/color]

re: STORED PROCEDURE - passing table name as a parameter

Steve (sjc4914@yahoo.com) writes:[color=blue]
> This is a "commercial" database solution purchased to record and report
> environmental data. I don't like the design for all the reasons already
> posted, and more. Though I had nothing to do with the selection,
> unfortunately, I am charged with completing a task, so like it or not, it
> is on my plate.
> Terry Kreft's idea looks workable, but not optimum. It would seem like >
> there would be a way to get around pasting strings together and then
> executing the string. I was wondering if there was a way to use a
> subquery to return a table reference. It is a rather obtuse way of
> getting there, and I haven't investigated it yet. Any alternate
> solutions anyone can think of?[/color]

David's suggestion of a view is a possible way, although I don't think it
can be an indexed view, as indexed views can not include the UNION keyword.

The view definition would look like:

SELECT tablename = 'tbl1', * FROM tbl1
UNION ALL
SELECT tablename = 'tbl2', * FROM tbl2
...

When you query the view as:

SELECT ...
FROM yourview
WHERE tablename= 'tbl2'
AND ...

I would expect SQL Server to be smart to not access the other tables.
They would appear in the query plan, but a startup expression would prevent
any access to the tables.

The other option beside dynamic SQL would be to write a small macro
processor that reads a common stub, and the substitutes the table name,
so that you have one procedure per table.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

re: STORED PROCEDURE - passing table name as a parameter

Erland Sommarskog wrote:[color=blue]
> David's suggestion of a view is a possible way, although I don't think it
> can be an indexed view, as indexed views can not include the UNION keyword.
>[/color]

Erland, you are right of course. What I meant to say was "partitioned
view", which may be feasible assuming the OP has a partitioning column.

--
David Portas
SQL Server MVP
--

re: STORED PROCEDURE - passing table name as a parameter

On Wed, 30 Nov 2005 09:50:18 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:
[color=blue]
>Steve (sjc4914@yahoo.com) writes:[color=green]
>> This is a "commercial" database solution purchased to record and report
>> environmental data. I don't like the design for all the reasons already
>> posted, and more. Though I had nothing to do with the selection,
>> unfortunately, I am charged with completing a task, so like it or not, it
>> is on my plate.
>> Terry Kreft's idea looks workable, but not optimum. It would seem like >
>> there would be a way to get around pasting strings together and then
>> executing the string. I was wondering if there was a way to use a
>> subquery to return a table reference. It is a rather obtuse way of
>> getting there, and I haven't investigated it yet. Any alternate
>> solutions anyone can think of?[/color]
>
>David's suggestion of a view is a possible way, although I don't think it
>can be an indexed view, as indexed views can not include the UNION keyword.
>
>The view definition would look like:
>
> SELECT tablename = 'tbl1', * FROM tbl1
> UNION ALL
> SELECT tablename = 'tbl2', * FROM tbl2
> ...
>
>When you query the view as:
>
> SELECT ...
> FROM yourview
> WHERE tablename= 'tbl2'
> AND ...
>
>I would expect SQL Server to be smart to not access the other tables.
>They would appear in the query plan, but a startup expression would prevent
>any access to the tables.
>
>The other option beside dynamic SQL would be to write a small macro
>processor that reads a common stub, and the substitutes the table name,
>so that you have one procedure per table.
>[/color]

I can't tell if the OP is dealing with a fixed set of tables, or if new tables
are bring continuously created. If the former, then I guess the view would
work. If the later, then it would not.

re: STORED PROCEDURE - passing table name as a parameter

This would be a nice solution with a reasonable number of tables but as the
OP said " There are literally hundreds of these tables ...", I thought
concatenation of a SQL string would be a better solution.


--
Terry Kreft



"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns971E6DF79130AYazorman@127.0.0.1...[color=blue]
> Steve (sjc4914@yahoo.com) writes:[color=green]
>> This is a "commercial" database solution purchased to record and report
>> environmental data. I don't like the design for all the reasons already
>> posted, and more. Though I had nothing to do with the selection,
>> unfortunately, I am charged with completing a task, so like it or not, it
>> is on my plate.
>> Terry Kreft's idea looks workable, but not optimum. It would seem like >
>> there would be a way to get around pasting strings together and then
>> executing the string. I was wondering if there was a way to use a
>> subquery to return a table reference. It is a rather obtuse way of
>> getting there, and I haven't investigated it yet. Any alternate
>> solutions anyone can think of?[/color]
>
> David's suggestion of a view is a possible way, although I don't think it
> can be an indexed view, as indexed views can not include the UNION
> keyword.
>
> The view definition would look like:
>
> SELECT tablename = 'tbl1', * FROM tbl1
> UNION ALL
> SELECT tablename = 'tbl2', * FROM tbl2
> ...
>
> When you query the view as:
>
> SELECT ...
> FROM yourview
> WHERE tablename= 'tbl2'
> AND ...
>
> I would expect SQL Server to be smart to not access the other tables.
> They would appear in the query plan, but a startup expression would
> prevent
> any access to the tables.
>
> The other option beside dynamic SQL would be to write a small macro
> processor that reads a common stub, and the substitutes the table name,
> so that you have one procedure per table.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]

re: STORED PROCEDURE - passing table name as a parameter

Steve Jorgensen (nospam@nospam.nospam) writes:[color=blue]
> I can't tell if the OP is dealing with a fixed set of tables, or if new
> tables are bring continuously created. If the former, then I guess the
> view would work. If the later, then it would not.[/color]

In such case, you would have to regenerate the view dynamically. Hey, in
SQL 2005 you could set up a database trigger and change the view definition
as soon as a table is created or dropped.

Of course, it is a trade-off where you want the dynamic stuff. One advantage
with creating the view dynamically is that this can be performed in an
admin job. Dynamic SQL in the procedures, requires that the user who runs
it have direct permissions to the tables.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

re: STORED PROCEDURE - passing table name as a parameter

Erland Sommarskog wrote:
[color=blue]
> Steve Jorgensen (nospam@nospam.nospam) writes:[color=green]
>> I can't tell if the OP is dealing with a fixed set of tables, or if new
>> tables are bring continuously created. If the former, then I guess the
>> view would work. If the later, then it would not.[/color]
>
> In such case, you would have to regenerate the view dynamically. Hey, in
> SQL 2005 you could set up a database trigger and change the view
> definition as soon as a table is created or dropped.
>
> Of course, it is a trade-off where you want the dynamic stuff. One
> advantage with creating the view dynamically is that this can be performed
> in an admin job. Dynamic SQL in the procedures, requires that the user who
> runs it have direct permissions to the tables.
>[/color]
The double-bad news is that table are created or dropped frequently, at
least during the initial deployment, and the database is SQL Server 2000,
always. Company standards, ya' know. Can you point me to a good tutorial or
book on creating dynamic views? That would solve another data access
problem that is looming on the horizon -- I can feel it coming. The view
could be regenerated daily during off-hours, and that would be sufficient.

BTW: I tried the suggestion from Terry Kreft and got things working.

Best regards,
Steve

re: STORED PROCEDURE - passing table name as a parameter

Steve (sjc4914@yahoo.com) writes:[color=blue]
> The double-bad news is that table are created or dropped frequently, at
> least during the initial deployment[/color]

Congratulations. :-(
[color=blue]
> Can you point me to a good tutorial or book on creating dynamic views?
> That would solve another data access problem that is looming on the
> horizon -- I can feel it coming. The view could be regenerated daily
> during off-hours, and that would be sufficient.[/color]

"Dynamic views" are not dynamic in the true sense, they are just dynamically
generated. All you need is means to identify the tables in question. For
instance say all these tables opens with the string 'lb1table'. Then you
would set up a cursor over

SELECT name FROM sysobjects WHERE name LIKE 'lbltable%'

And then foreach row add to the view definition

SELECT @viewdef = @viewdef +
'SELECT tblname = ''' + @name + ''', * FROM ' + @name +
' UNION ALL'

At the end of the loop you need get rid of that last UNION ALL. Eventually
you would say EXEC(@viewdef).

Here I used T-SQL, but you could just as well write a program in VB, C,
Perl whatever that did the same thing. The problem with doing this in
T-SQL, is that you get problems if the view definition does not fit into
a varchar(8000).

The tricky part may be to identify which tables to include. Possibly you
will need to query other system tables, for instance syscolumns.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

re: STORED PROCEDURE - passing table name as a parameter

Erland Sommarskog wrote:
[color=blue]
> Steve (sjc4914@yahoo.com) writes:[color=green]
>> The double-bad news is that table are created or dropped frequently, at
>> least during the initial deployment[/color]
>
> Congratulations. :-(
>[color=green]
>> Can you point me to a good tutorial or book on creating dynamic views?
>> That would solve another data access problem that is looming on the
>> horizon -- I can feel it coming. The view could be regenerated daily
>> during off-hours, and that would be sufficient.[/color]
>
> "Dynamic views" are not dynamic in the true sense, they are just
> dynamically generated. All you need is means to identify the tables in
> question. For instance say all these tables opens with the string
> 'lb1table'. Then you would set up a cursor over
>
> SELECT name FROM sysobjects WHERE name LIKE 'lbltable%'
>
> And then foreach row add to the view definition
>
> SELECT @viewdef = @viewdef +
> 'SELECT tblname = ''' + @name + ''', * FROM ' + @name
> + ' UNION ALL'
>
> At the end of the loop you need get rid of that last UNION ALL. Eventually
> you would say EXEC(@viewdef).
>
> Here I used T-SQL, but you could just as well write a program in VB, C,
> Perl whatever that did the same thing. The problem with doing this in
> T-SQL, is that you get problems if the view definition does not fit into
> a varchar(8000).
>
> The tricky part may be to identify which tables to include. Possibly you
> will need to query other system tables, for instance syscolumns.
>
>[/color]
This looks somewhat promising. I'll give it a try over the weekend and let
you know how if it works out. I tried to move from a stored procedure to a
stored function yesterday and ended up frustrated. I could almost get it to
work, but I couldn't come up with a way to return the scalar value from the
EXECUTE @sql. There is still a lot I have to learn. Old dog, new tricks :)

Steve Caldwell

re: STORED PROCEDURE - passing table name as a parameter

Steve (sjc4914@yahoo.com) writes:[color=blue]
> This looks somewhat promising. I'll give it a try over the weekend and
> let you know how if it works out. I tried to move from a stored
> procedure to a stored function yesterday and ended up frustrated. I
> could almost get it to work, but I couldn't come up with a way to return
> the scalar value from the EXECUTE @sql.[/color]

You can use sp_executesql for that:

EXEC sp_executesql N'SELECT @cnt = COUNT(*) FROM tbl', N'@cnt int OUTPUT',
@cnt = @count OUTPUT

However, you cannot use dynamic SQL in user-defined functions.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx