SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dumb Question


Dumb Question

Author
Message
Kayal-717623
Kayal-717623
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 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 Wink

Thanks for your time.
Kayal-717623
Kayal-717623
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 290
I know it can be done by dynamic sql but is there any way to do it without dynamic sql?

Thanks,
kayal
Raleigh Beringer
Raleigh Beringer
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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.
steveb.
steveb.
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10032 Visits: 7195
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 ..
Kayal-717623
Kayal-717623
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 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.
Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12509 Visits: 23078
You cannot do this without dynamic SQL.
GSquared
GSquared
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54847 Visits: 9730
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
Kayal-717623
Kayal-717623
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 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.
GSquared
GSquared
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54847 Visits: 9730
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
Kayal-717623
Kayal-717623
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 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 Smile

Thanks Again,
Kayal
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search