Table Column as Variable
-
Hi all,
I have an after update trigger that bascially looks for the fields that were updated and log that information.
I am getting the column name and storing it in a variable :@colname. I want to get the column contents.
What I need is something like this:
SELECT @tcolname = ('SELECT '+@colname+' FROM deleted')
EXEC (@tcolname)
However, this does not work since deleted isn't recognized.
Another way that I tried that didn't work:
SELECT @colname FROM deleted;
In this case, the table was recognized, but @colname is the name of the column, e.g. FIRSTNAME, and not, e.g. John.
Please help..
-
Welcome to the limited and confusing and mind boggling world of dynamic sql. My guess is that 'deleted' being a 'special table' of sorts is not recognized inside the dynamic sql stored procedure, i.e. it doesn't get passed 'automatically' as you would like. A dynamically called stored procedure is in a dark world and unless you pass things to it explicitly it will know nothing about who or what called it unless you help. In this case I think there is no way.
-
I have no hope of getting this resolved? (sigh)
I'll post the code tomorrow, as it's on another computer that I can't get to right now. However, I don't think that it matters as I am sure that it's getting the correct column name since, if I change the table name in:
SELECT @tcolname = ('SELECT '+@colname+' FROM deleted')
EXEC (@tcolname)
to let's say app_user, it returns the correct column results, i.e. if FIRSTNAME is passed in, then all the FIRSTNAMEs from the app_user table are printed.
-
SELECT *
INTO #deleted
FROM deleted
-- you may wish to work out data some way here, e.g. delete duplications, apply trims for char values, etc.
SELECT @tcolname = ('SELECT '+@colname+' FROM #deleted')
EXEC (@tcolname)
_____________
Code for TallyGenerator -
'deleted' is a special table available within the context of a trigger. The dynamic sql doesn't know it's being called by a trigger. But the use of a temporary table suggested by Sergiy seems like a good prospect.
-
So, I added the following lines as advised:
SELECT * INTO #deleted FROM deleted
SELECT @tcolname = ('SELECT '+@colname+' FROM #deleted')
EXEC (@tcolname)
and this is the error I get:
"There is already an object named '#deleted' in the database."
As for the sql used to get the column name:
SELECT @colname = COLUMN_NAME from Information_Schema.Columns WHERE @field=ORDINAL_POSITION AND TABLE_NAME=@tblname
However, like I said, I don't think that this affects anything, taking into consideration that I could be wrong
-
So change the select to read:
SELECT * INTO #d2 FROM deleted
SELECT @tcolname = ('SELECT '+@colname+' FROM #d2')
EXEC (@tcolname)
That will eliminate the error of #deleted already being there
Live to Throw
Throw to Live
Will Summers -
I should have mentioned that I tried changing the name : #deleted to #del with the same error:
"There is already an object named '#del' in the database."
In other words, I don't think it has to do with the name of the table, as I am getting the same error despite the name.
-
You probably need to read BOL about temporary tables.
Where and when they are created, when dropped, what is the scope for it, etc.
_____________
Code for TallyGenerator -
What is the overall problem you are trying to solve? Are you are trying to write a general purpose trigger which logs changes on an arbitrary table to a central table of the form
create table changes(
tabname varchar(50),
colname varchar(50),
oldvalue varchar(100),
newvalue varchar(100),
changedon datetime)
My big question is: why is your column name in a variable?
In any case, this kind of trigger is hard to generalize because typically to see changes you will need to link the deleted table with the inserted table through specific columns that represent a unique key and then compare the remaining columns.
-
So, I used a temporary table to get the value, with the code below:
--check if table exists and if it does, drop it
If Object_Id('tempdb..#temp') is Not Null
Drop table #temp
SELECT * INTO #temp FROM deleted
SELECT @tcolname = ('SELECT '+@colname+' FROM #temp')
EXEC (@tcolname)
I think the error posted earlier was caused by a loop.
Thanks for all the help.
However, I need one more step, and that's for whatever value is returned, for it to be stored in a variable.
I need something like this:
SELECT @tcolname = ('SELECT '+@colcon+'='+@colname+' FROM #temp')
However, as you might assume, it's a null value is stored in @colcon.
Any suggestions?
Please help...
-
As for what I am trying to achieve...
I want to log a user's update action on a specific table. However, I do not want to go checking one-for-one the fields updated, so I have decided to write a loop that would check if the field is updated and then store it in a variable. The loop is checking the substring of COLUMNS_UPDATED() to get the column.
I am storing the column name as well as the old value of the column (retrieved from the deleted table) among other values.
I am getting all the values I need except the old value of the column since I am passing in a variable as the column name. I am now able to get the value from the deleted table (as shown in the code above).
What I need now, is for that value to be stored in a variable so that I can pass it to a table to be stored.
I hope you understand? Feel free to ask any questions...
-
...for that value to be stored in a variable... Use sp_executesql like this
DECLARE @result varchar(255)
SET @sql = 'SELECT @result = ' + @colname + ' FROM #temp'
EXEC sp_executesql @sql, N'@result varchar(255) OUTPUT', @result OUTPUT
however you may have to CAST the column you are selecting to match @result or make sure it is of the correct datatype
Far away is close at hand in the images of elsewhere.
Anon. -
My problem is resolved. The last step was done using David's method (as above). Thanks alot.
Thank you so much everyone! 🙂 Really couldn't have done it without you..
By the way...what do you think of the method I used? ....This should have really been my first quest no?...I needed to save on code and be able to reuse existing code...that's why I chose to go down this path
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply