473,836 Members | 1,476 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

get current table name in stored procedure

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
Jul 20 '05 #1
6 18741
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
--
Jul 20 '05 #2
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
--
Jul 20 '05 #3
Michael Spiegel (ms******@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
@@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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
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" <RE************ *************** *@acm.org> wrote in message news:<yO******* *************@g iganews.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.

Jul 20 '05 #5
>> 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.
Jul 20 '05 #6
Michael Spiegel (ms******@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(par ent_obj)
FROM sysobjects
WHERE id = @@procid
go
INSERT nisse VALUES (21)
go
DROP TABLE nisse
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3583
by: T.S.Negi | last post by:
Dear Techies, I making one stored procedure, which does some operation based on an interface hash (#) table ---- name #mydata. This stored has two section of code (seperated by parameter value 0 and 1) But hash table #mydata (same name) number/name of columns changes as per call 0 or 1.
10
5643
by: serge | last post by:
Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I create a single View and specify also in this View the WHERE clause that is common in these stored procedures, I will have the new stored procecures changed to be like:
7
7993
by: Mike Hubbard | last post by:
I have read many many messages about temporary tables and stored procedures. Still, I am struggling with a simple concept. I have a java program which creates a temporary table. I now want to access (read) this table in a stored procedure (written in SQL). This stored procedure is called from the same java program. My question is - how do I "compile" the stored procedure and avoid the error message: DB21034E The command was processed...
0
1653
by: joe pribele | last post by:
I have this stored procedure that takes some xml as input. What I need to is use xml as a table so that I can join other tables to the xml and get information back that matches the criteria. I can use dxxshredxml to put it into a regular table and everything works great. but when I try to put it into a temp table it doesn't work. I get an error saying Session.ACCESSMASK doesn't have a column name ACCESS_MASK_ID. Do I have to use a...
2
3858
by: claus.hirth | last post by:
I wrote a stored procedure that uses a prepared INSERT INTO statement in order to play with the PREPARE and EXECUTE keywords. In transcript 1 below the call to that stored procedure does not work. Yet transcript 2 shows that the stored procedure does its job, if only the table name is provided directly in the statement text string, instead of as a host variable to a prepared statement like in 'EXECUTE stmt USING tabName, fldValue'. --...
1
3722
by: Lauren Quantrell | last post by:
I have read the newsgroups and see this is a common issue but I saw no resolution for it: I have an Access2K frotn end and SQL Server 2K backend. In access, I create a temp table using code in a module: strSQL = "CREATE TABLE #tempTableName ("TempID int NOT NULL IDENTITIY (1,1) PRIMARY KEY, Field1 int...) CurrentProject.Connection.Execute strSQL,,adCmdText so far so good...
0
4731
by: amit | last post by:
The Table tt_local will be created when you execute the procedure. The table does not exist when you compile it. There are two options 1. Create the global temporary table manually Remove the creation of the table from the proc and then compile it. 2. Run the insert also as dynamic sql. Remember dynamic sqls don't go through semantic check at compile time.
3
7749
by: dmorand | last post by:
I'm very new to stored procedures so this is probably something very dumb. I want to pass a name of a table to be created to my stored procedure. I'm using the variable @tableName in the code below but I'm getting an error: Server: Msg 170, Level 15, State 1, Procedure usp_CodeGreyData, Line 22 Line 22: Incorrect syntax near '@tableName'. Server: Msg 137, Level 15, State 1, Procedure usp_CodeGreyData, Line 29 Must declare the variable...
2
14670
by: qwedster | last post by:
Folk! How to programattically check if null value exists in database table (using stored procedure)? I know it's possble in the Query Analyzer (see last SQL query batch statements)? But how can I pass null value as parameter to the database stored procedure programattically using C#? Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value...
0
9813
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9665
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10834
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10541
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10584
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10248
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6976
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4446
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.