Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Dumb Question Expand / Collapse
Author
Message
Posted Friday, July 18, 2008 10:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 6, 2012 10:08 AM
Points: 78, Visits: 290
Hi all,

i have something like the below:

declare @ss varchar(30)
set @ss='ProductID'
SELECT @SS from Product

If i execute the above, 'ProductID' is returned. But I want to retrieve the value of productID? How will i get the value of the productid instead of the string 'ProductID'

i know this is probably the dumbest question ever ;)

Thanks for your time.
Post #536927
Posted Friday, July 18, 2008 10:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 6, 2012 10:08 AM
Points: 78, Visits: 290
I know it can be done by dynamic sql but is there any way to do it without dynamic sql?

Thanks,
kayal
Post #536929
Posted Friday, July 18, 2008 10:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 8:54 AM
Points: 4, Visits: 45
You are explicity setting the value for @ss as ProductID

Try something like the following

Set @ss = (SELECT ProductID FROM SomeTable)

Note that you may have to set a top 1 or where clause to ensure only 1 result is returned.

Post #536930
Posted Friday, July 18, 2008 10:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
Quite a tricky question, I am not aware of a way that it can be done without using dynamic SQL.
Am interested if anyone has a solution ..
Post #536941
Posted Friday, July 18, 2008 10:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 6, 2012 10:08 AM
Points: 78, Visits: 290
Thanks Raleigh. I dont want to assign the result of the select to a variable but i want to use the variable(which has the field name) in the select list to get the value of the field.

The reason why i need this is in the audit table trigger, I get the field name of the primary key
SELECT @PrimaryKey = COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @table_name
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

I want to use the @PrimaryKey in my select to get the primary key value. Also using the COLUMNS_UPDATED() function i find out the fieldnames which was updated. I need to use the field names in the select list to get the value of the field.

Thank You. i appreciate your reply.
Post #536942
Posted Friday, July 18, 2008 10:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
You cannot do this without dynamic SQL.
Post #536953
Posted Friday, July 18, 2008 11:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
Since triggers have to be created on each table that you're going to log, why not just put the primary key in the trigger directly? This seems a little too generic.

- 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
Post #536978
Posted Friday, July 18, 2008 12:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 6, 2012 10:08 AM
Points: 78, Visits: 290
Thanks Micheal and Gsquared for the replies. i really appreciate it.

GSquared,

You are right. But the maintenance will be really difficult right. Any change in the table, everybody forgets to update the trigger right. So I am trying to make it as generic as possible. Do you think that will affect the performance badly? Thats why i am tring to avoid the dynamic sql. Performance is ofcourse an important criteria for me. But my manager donot want me to hard code things as well. I dono what is a best solution here.

Any suggestion is greatly appreciated.

Post #537010
Posted Friday, July 18, 2008 1:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
I wrote a couple of articles on logging and auditing a little while back. They might help. They're at:

http://www.sqlservercentral.com/articles/Auditing/63247/
http://www.sqlservercentral.com/articles/Auditing/63248/

The discussions have a lot of good data in them as well.

Check those out, and if they don't answer what you need, please let me know and I'll see if I can help clarify it.


- 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
Post #537021
Posted Friday, July 18, 2008 3:24 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 6, 2012 10:08 AM
Points: 78, Visits: 290
Hi Gsquared,

That was a quick reply. I read ur articles on auditing a month back. Those 2 articles was awesome and Only that got me started. Again now i am stuck in a confusion. Those articles have quite a bit of information. I have to re-visit them and read it again. That will resolve my problem i guess :)

Thanks Again,
Kayal
Post #537084
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse