Select a field wich name is in a variable (impossible?)
-
Hi ya all!
I'v been reading your posts for a long time.
Very good information here, and nice helping dudes.
I'm wondering if some of you could help me with this... I'v been searching all the net for a solution with no success. I'm working with a SQLServer2000
Here it goes:
This is a draft of the mission:
On every Clients insert, I trigger to fill a third table (Audit) containing the fields affected, and its value.
I have a cursor that iterates through a table (AuditFields) containing Field Names from other tables (Clients).
The problem:
I have to retrieve the value for a given field (wich name is in a variable) from table Clients (or whatever).
Of course the problem is that I have the field name in a variable... so I first tried this possible solutions:
A. Set @NEWVALUE = CAST((Select @FIELDNAME From inserted Where RegAudited = @REGAUDITED) as NVARCHAR(100))
But this returns the Field Name, not the value that contains the field
B. Execute('Select @NEWVAL = ins.' + @FIELDNAME + ' FROM inserted ins WHERE ins.RegAudited = ' + @REG)
But this also not working, because @NEWVAL is out of execute's scope
Please help me, these is my second week's new job, and dunnow how to solve it.
Thanks in advance!!
And sorry my poor english
-
If you know all the columns of your table than you should be able to write a simple query like.
SELECT CASE @fieldName WHEN colName1 THEN colName1
WHEN colName2 THEN colName2
WHEN colName3 THEN colName3
END
FROM table
WHERE .....xyz criteria........
-
And if you don't know all the columns and need dynamic SQL
B. Execute('Select @NEWVAL = ins.' + @FIELDNAME + ' FROM inserted ins WHERE ins.RegAudited = ' + @REG)
But this also not working, because @NEWVAL is out of execute's scope
Look up sp_executesql in books online for a way to get variables into and out of dynamic sql.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi! Thanks for your replyes.
No, I don't know the column names.
I'v been reading about sp_executesql.
May be I didn't fully understood the full power of sp_executesql; but it seems that it can only get variables in, NOT OUT.
My main problem is to get the select result out of the execute/sp_executesql function.
Thanks again!
-
You can get variables out. I find, personally, it's the main use of it.
DECLARE @RecordCount INT
EXEC sp_executesql N'SELECT @RecordCountOut=COUNT(*) FROM sysobjects', N'@RecordCountOut INT OUTPUT', @RecordCountOut=@RecordCount OUTPUT
SELECT @RecordCount
It's like a stored procedure with respect to output parameters
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Oh! I see.
Thanks man!!!!!!!!!
Ultrafast response
-
Pleasure, got nothing else to do right now.
(looks cautiously around for boss)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Oooops! Heaven will have to wait
This way I'm loosing Inserted table scope (this is a table made by the trigger)
SET @SQLSTRING = N'Select @FIELDOUT = Cast(ins.' + @FIELDNAME + N' as nvarchar(100)) From inserted ins Where ins.RegAudited = ' + cast(@REGAUDITED as varchar)
EXEC sp_executesql @SQLSTRING, N'@FIELDOUT nvarchar(100) OUTPUT', @FIELDOUT = @NEWVALUE OUTPUT
I'd pass Inserted as table to sp_executesql, but this way I expect very low performance, and as this sentence will be triggered on every DB transaction (very huge, receiving queries constantly all over the world), may be there's a better solution.
Sometimes I feel like T_SQL is a bit inconsistent, and when doing something very specific I'm pushed to bizarre coding
Any ideas?
-
Why not try to do this auditing the 'proper' way..?
I assume that you want to audit a bunch of tables, correct? A trigger is a part of a table, so there shouldn't be anything strange that the trigger should be aware of it's own table's columns. I don't see why dynamic SQL should be needed at all.
Just create a specific audit-trigger for the events to audit on each table. Then it's no problems at all for each trigger to access the rows affected and place those into it's audit table. If you want, you can place both the 'before' and 'after' versions into the audit table. After all, this is the main purpose, right? To quick and efficiently audit changes. Then do just that, and let the transaction continue, else you're bound to get horrible performance problems etc.
Don't be fooled that dynamic SQL will let you construct a 'generic-do-it-all' kind of trigger that will let you do 'less work' - it's the opposite. You'll end up paying for a dynamic SQL solution with 'more work and more problems' - as good as always...
/Kenneth
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply