Finding the current table name in the context of an insert statement

Question

Finding the current table name in the context of an insert statement

tuseau

SSC-Addicted

Points: 487
More actions
March 13, 2008 at 5:13 pm

#188033

Hi,
I am writing a script to perform a large number of insert statements. Since there is no way to define a global variable in T-SQL, I think I can accomplish the same thing with a user-defined function. What I need to do in the function is find the current table name from the context of the insert statement. For example:
the insert statement might be:
INSERT INTO table1 (ID, Firstname, Lastname) VALUES (function1(ID), 'John', 'Smith')
I want the function to be able to capture current table name from the context of the insert statement, so that it will do the same job for every different insert statement no matter what the table name is. I know you can use OBJECT_NAME(@@PROCID) for a procedure/trigger, but can you do the same for a table in an INSERT statement?
If not, does anyone know of a good way to imitate a "global" variable? i.e. a variable that has a lifespan longer than the current transaction.
Thanks!

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Make the UDF accept the table name as an additional parameter and pass it. Just remember that UDF's can't do Inserts, Updates, or Deletes on tables nor can it do an EXEC (or, at least they couldn't in 2k... not sure about 2k5... look in BOL to make sure).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Jeff Moden (3/13/2008)
Make the UDF accept the table name as an additional parameter and pass it. Just remember that UDF's can't do Inserts, Updates, or Deletes on tables nor can it do an EXEC (or, at least they couldn't in 2k... not sure about 2k5... look in BOL to make sure).

Nope - that still hasn't changed. The only caveat to the list above is you can execute Extended stored procs from within a function (but I think that was true in 2000 as well).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

That would be correct (about the XP's in 2k). Didn't mention those because didn't think they would apply here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tuseau SSC-Addicted Points: 487 More actions · Answer 4

Thanks for the suggestion - I was hoping for a system function which would return the table name but I guss it doesn't exist.

However, I wrote the following function which should do what I want, but SQL Server 2005 keeps returning the error "Must declare the table variable "@currentTable". " Why does it not realize that @currentTable is an input parameter?

CREATE FUNCTION nextID(@currentTable nvarchar(50))

RETURNS INT

AS

BEGIN

RETURN (SELECT max(Id) + 1 FROM @currentTable)

END

GSquared SSC Guru Points: 260824 More actions · Answer 5

tuseau (3/13/2008)
Hi,
If not, does anyone know of a good way to imitate a "global" variable? i.e. a variable that has a lifespan longer than the current transaction.

Store it in a table.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

You're trying to back-door in what would be called dynamic SQL, which unfortunately requires being executed from EXEC or sp_executeSQL (both of which are prohibited in a function).

That being said - why are you trying to retrieve the ID in that way? What are you planning on doing with it? Having seen this kind of code before, it tends to lead to the dark side and much pain along the way. There are lots of "better ways" to get that info, if I can anticiate what you're using it for.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

GSquared SSC Guru Points: 260824 More actions · Answer 7

tuseau (3/17/2008)
Thanks for the suggestion - I was hoping for a system function which would return the table name but I guss it doesn't exist.
However, I wrote the following function which should do what I want, but SQL Server 2005 keeps returning the error "Must declare the table variable "@currentTable". " Why does it not realize that @currentTable is an input parameter?
CREATE FUNCTION nextID(@currentTable nvarchar(50))
RETURNS INT
AS
BEGIN
RETURN (SELECT max(Id) + 1 FROM @currentTable)
END

SQL can't use variables that way. You'd have to set it up as dynamic SQL.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 8

Besides anything else that may come up in this thread, I have to ask, why not just use an auto-increment ("identity") value in the tables you want to insert to?

That way, you don't have to build a function to get the "next" ID number. The database will do it for you.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

tuseau (3/17/2008)
Thanks for the suggestion - I was hoping for a system function which would return the table name but I guss it doesn't exist.
However, I wrote the following function which should do what I want, but SQL Server 2005 keeps returning the error "Must declare the table variable "@currentTable". " Why does it not realize that @currentTable is an input parameter?
CREATE FUNCTION nextID(@currentTable nvarchar(50))
RETURNS INT
AS
BEGIN
RETURN (SELECT max(Id) + 1 FROM @currentTable)
END

Because in the SQL syntax, you cannot refer to a table's Name as a variable. It has to be a literal name (quoted with "[".."]" if necessary).

You have to use Dynamic SQL (build a string and then execute it) or xp_'s or CLR or certain system procedures(which are doing the same tricks internally) to get around it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Oops, I didn't realize that we had already piled on that one. 😀

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 11

If you're trying to figure out wich objects are used by your proc,

check out your xml-plan !

you could off course equipt your sproc so it returen the objectid for you.

select object_id('yourtablename') as The_Table_In_Context

you could even equipt it with

select object_name(object_id('yourtablename')) as The_Table_In_Context

, object_schema_name('yourtablename') as The_SchemaName_In_Context

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

tuseau SSC-Addicted Points: 487 More actions · Answer 12

Thanks for the help everyone...I have figured it out (the problem was a bit more complex than how I was explaining it).

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

Would you share what you figured out, please? We'd like to know how you did it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tuseau SSC-Addicted Points: 487 More actions · Answer 14

The problem itself wasn't quite what I was making of it. I didn't need to return the table name from the context of the insert statement after all.

Basically I was needing to create a very large update script which was independent of the identity column. So, if the database had been purged and then the script was re-run, then the ID column would not be the same (since SQL Server keeps a record of the running value each identity column). The existing script was written by someone else and it involved referencing hard-coded ID values - and the database has a lot of foreign keys, so for each insert/update statement, it referenced an identity explicitly.

I was able to get round the main issue by using the ident_current('TABLENAME') system function for populating most foreign keys (just subtracting the number of "steps back" need to go to find the row I am referncing). I then wrote some user-defined functions to look up the identity value for other tables which I knew had a unique identifier/constant value.

Thanks again to all who helped.