Tags: current, database, familiar, injava, microsoft, mysql, oracle, pointer, procedure, server, sql, stored, table, tablefrom

get current table name in stored procedure

On Database » Microsoft SQL Server

6,699 words with 6 Comments; publish: Thu, 27 Dec 2007 20:03:00 GMT; (250171.88, « »)

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

All Comments

Leave a comment...

  • 6 Comments
    • 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

      --

      #1; Thu, 27 Dec 2007 20:05:00 GMT
    • 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

      --

      #2; Thu, 27 Dec 2007 20:06:00 GMT
    • Michael Spiegel (mspiegel.sqlserver.questionfor.info.sccs.swarthmore.edu) writes:

      > 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?

      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

      .sqlserver.questionfor.info..sqlserver.questionfor.info.procid which returns the object id for the currently executing object.

      But since a table never can execute, .sqlserver.questionfor.info..sqlserver.questionfor.info.procid can never refer to a table.

      --

      Erland Sommarskog, SQL Server MVP, esquel.sqlserver.questionfor.info.sommarskog.se

      Books Online for SQL Server SP3 at

      http://www.microsoft.com/sql/techin.../2000/books.asp

      #3; Thu, 27 Dec 2007 20:07:00 GMT
    • 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_dportas.sqlserver.questionfor.info.acm.org> wrote in message news:<yO6dnRg_Q5VUgEDdRVn-sQ.sqlserver.questionfor.info.giganews.com>...

      > 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.

      #4; Thu, 27 Dec 2007 20:08:00 GMT
    • >> 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.

      #5; Thu, 27 Dec 2007 20:09:00 GMT
    • Michael Spiegel (mspiegel.sqlserver.questionfor.info.sccs.swarthmore.edu) writes:

      > 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

      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(parent_obj)

      FROM sysobjects

      WHERE id = .sqlserver.questionfor.info..sqlserver.questionfor.info.procid

      go

      INSERT nisse VALUES (21)

      go

      DROP TABLE nisse

      --

      Erland Sommarskog, SQL Server MVP, esquel.sqlserver.questionfor.info.sommarskog.se

      Books Online for SQL Server SP3 at

      http://www.microsoft.com/sql/techin.../2000/books.asp

      #6; Thu, 27 Dec 2007 20:10:00 GMT