get current table name in stored procedure

[Michael Spiegel]

Hi,

I have a stored procedure that needs to know the name of the table
from which it is called. If you are familiar with a "this" pointer in
Java or C++, that's very similar to what I need. I know I can use
db_name() to retrieve the database name, but how do retrieve the table
name?

Thanks,
--Michael

[David Portas]

Re: get current table name in stored procedure

That doesn't make sense. A stored procedure can't be called by a table -
tables are just data structures. In SQL there is no concept of a "current"
table - all tables are available at all times.

Maybe you are referring to Triggers, but since a trigger can only apply to a
single table there shouldn't be any doubt about which table caused the
trigger to fire.

A user-defined function can be called from within a query, view or computed
column but to supply a UDF with information such as a table name you would
need to pass that information as a function parameter.

--
David Portas
SQL Server MVP
--

[David Portas]

Re: get current table name in stored procedure

Just to expand on my previous answer. Suppose you wanted to call the same SP
from triggers on several tables. If required you can pass the table name to
the SP from the trigger code:

CREATE TRIGGER trg_Table1 ON Table1 FOR INSERT
AS
EXEC usp_Something 'TABLE1'

GO

CREATE TRIGGER trg_Table2 ON Table2 FOR INSERT
AS
EXEC usp_Something 'TABLE2'

However, the stored procedure won't be able to access the INSERTED and
DELETED virtual tables so any functionality that needs to reference the
changed data would still have to go in the trigger itself.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Re: get current table name in stored procedure

Michael Spiegel (mspiegel@sccs. swarthmore.edu) writes:[color=blue]
> I have a stored procedure that needs to know the name of the table
> from which it is called. If you are familiar with a "this" pointer in
> Java or C++, that's very similar to what I need. I know I can use
> db_name() to retrieve the database name, but how do retrieve the table
> name?[/color]

To add to David's answer: a general caveat about SQL programming. SQL
is a quite different universe from Java/C++, and requires a different
mindset.

Generally, T-SQL is weaker on pure programming constructs. (On the other
hand is immensly much more powerful on data access.)

The only feature that is remotely close to what you are asking for is
@@procid which returns the object id for the currently executing object.
But since a table never can execute, @@procid can never refer to a table.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Michael Spiegel]

Re: get current table name in stored procedure

Yes, my apologies, I was referring to a trigger. And you are correct
in that it's possible to always statically determine what table is in
my context, based on the trigger. But I wish to write a series of
triggers that all perform nearly identical tasks, except they act
w.r.t. the table which called them. Like so:


CREATE TRIGGER trg_Table1 ON Table1 FOR INSERT
AS
INSERT INTO foo (invocation) VALUES (this)
GO

CREATE TRIGGER trg_Table2 ON Table2 FOR INSERT
AS
INSERT INTO foo (invocation) VALUES (this)
GO

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message news:<yO6dnRg_Q 5VUgEDdRVn-sQ@giganews.com >...[color=blue]
> That doesn't make sense. A stored procedure can't be called by a table -
> tables are just data structures. In SQL there is no concept of a "current"
> table - all tables are available at all times.
>
> Maybe you are referring to Triggers, but since a trigger can only apply to a
> single table there shouldn't be any doubt about which table caused the
> trigger to fire.
>
> A user-defined function can be called from within a query, view or computed
> column but to supply a UDF with information such as a table name you would
> need to pass that information as a function parameter.[/color]

[--CELKO--]

Re: get current table name in stored procedure

>> I have a stored procedure that needs to know the name of the table
from which it is called. If you are familiar with a "this" pointer in
Java or C++, that's very similar to what I need. <<

Your conceptual model is completely wrong.

The entire database is the "unit of work" -- you log onto the entire
database, not just to a few tables. You might have access to a
subset, but the rest of the schema is still there; that is why DRI
works. Stored procedures work at the schema level.

"Pointer" is an obscene word in RDBMS. A pointer is the lowest kind
of PHYSICAL locator possible and we want LOGICAL references to data
elements.

What I hope you are *not* trying do is use a table name as a parameter
or global variable in a stored procedure. That would be a complete
violation of cohesion and good software engineering regardless of the
language used.

[Erland Sommarskog]

Re: get current table name in stored procedure

Michael Spiegel (mspiegel@sccs. swarthmore.edu) writes:[color=blue]
> Yes, my apologies, I was referring to a trigger. And you are correct
> in that it's possible to always statically determine what table is in
> my context, based on the trigger. But I wish to write a series of
> triggers that all perform nearly identical tasks, except they act
> w.r.t. the table which called them. Like so:
>
>
> CREATE TRIGGER trg_Table1 ON Table1 FOR INSERT
> AS
> INSERT INTO foo (invocation) VALUES (this)
> GO
>
> CREATE TRIGGER trg_Table2 ON Table2 FOR INSERT
> AS
> INSERT INTO foo (invocation) VALUES (this)
> GO[/color]

A trigger is always bound to table, so, yes, you always know in the table
of which table you are.

One way to go, would be to generate the triggers with some tool, that
would take the table name as parameter.

But if you want to dig out the table name from the trigger, this is
actually possible:

CREATE TABLE nisse (a int NOT NULL)
go
CREATE TRIGGER nisse_tri ON nisse FOR insert AS

SELECT parent_table = object_name(par ent_obj)
FROM sysobjects
WHERE id = @@procid
go
INSERT nisse VALUES (21)
go
DROP TABLE nisse


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp