February 18, 2012 at 12:22 am
Hi,
There is a table with 30 to 40 columns and hardly few columns may get updated by the user. We can't say which column(s) may get updated, so its not advisable to use all the 30 to 40 columns in the UPDATE query.
Any method is there where in the query or stored procedure, we can find out which column(s) now affecting and use only those columns in the UPDATE query?
February 19, 2012 at 3:52 pm
rajeev_vandakar-782919 (2/18/2012)
...Any method is there where in the query or stored procedure, we can find out which column(s) now affecting and use only those columns in the UPDATE query?
Whatever you will write in you stored proc or query, this will determine what will be affected!
If you want to pass into stored proc the name of column and new value, then you can write dynamic SQL to update this column. However this design will look outright stupid.
Do you mean is anyway to determine if optional input parameters were specified?
It's not totally possible, and anyway you would still better to write normal sql and update all 30-40 columns.
February 19, 2012 at 10:16 pm
Eugene Elutin (2/19/2012)
rajeev_vandakar-782919 (2/18/2012)
It's not totally possible, and anyway you would still better to write normal sql and update all 30-40 columns.
Not if you have triggers based on some of the individual columns.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 19, 2012 at 10:27 pm
rajeev_vandakar-782919 (2/18/2012)
Hi,There is a table with 30 to 40 columns and hardly few columns may get updated by the user. We can't say which column(s) may get updated, so its not advisable to use all the 30 to 40 columns in the UPDATE query.
Any method is there where in the query or stored procedure, we can find out which column(s) now affecting and use only those columns in the UPDATE query?
For such a thing, I generally have the users pass "parameter pairs" where the column name and the new value of the column are passed together. If more than one column needs to be updated then or course, you pass more than 1 parameter pair.
In Sql Server 2005, you're a bit limited on the methods you can use.
1. Dynamic SQL embedded in your front end code. This is probably the worst of all options IMHO.
2. Pass XML to a stored proc, parse it, and gen "safe" Dynamic SQL from it.
3. Pass delimited values as a single string, parse it, and gen "safe" Dynamic SQL from it.
For Item 3, please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/63003/
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2012 at 12:08 am
Jeff Moden (2/19/2012)
rajeev_vandakar-782919 (2/18/2012)
Hi,There is a table with 30 to 40 columns and hardly few columns may get updated by the user. We can't say which column(s) may get updated, so its not advisable to use all the 30 to 40 columns in the UPDATE query.
Any method is there where in the query or stored procedure, we can find out which column(s) now affecting and use only those columns in the UPDATE query?
For such a thing, I generally have the users pass "parameter pairs" where the column name and the new value of the column are passed together. If more than one column needs to be updated then or course, you pass more than 1 parameter pair.
In Sql Server 2005, you're a bit limited on the methods you can use.
1. Dynamic SQL embedded in your front end code. This is probably the worst of all options IMHO.
2. Pass XML to a stored proc, parse it, and gen "safe" Dynamic SQL from it.
3. Pass delimited values as a single string, parse it, and gen "safe" Dynamic SQL from it.
For Item 3, please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/63003/%5B/quote%5D
I agree that option 1 is by far the worst.
Generating "safe" dynamic SQL in options 2 and 3 is non-trivial. Not only do the column names extracted from the string have to be validated (that's the easy bit - the catalogue view sys.columns makes it easy, because any purported column name string found there is safe - and any string not found there can safely be treated as unsafe as a column name and cause an error exit from the SP) but the replacement values extracted from the string have to be validated too (or each one has to be modified so that it is safe even if the substring originally extracted wasn't); but although it's not rocket science it's very easy to get wrong.
Another option is to design the system so that it's safe for update triggers to behave as if all columns are changed in a row whose primary key is in inserted is not in deleted, and for rows where the primary key is in both treat all columns which are the same in the corresponding inserted and deleted rows as unchanged; then it's going to be safe to set all columns in every update query, because the update triggers won't do anything different because an unchanged column was set in the update query. Of course that's maybe not easy to impose as an afterthought on a system that wasn't designed that way.
Tom
February 20, 2012 at 3:39 pm
This may not be the best way, but it works for my current situation. I create a procedure that lists all the columns as parameters, with "default" values (with the exception of the key columns). Yes, the "default" values are "magic" values, but I can't find another way in SQL Server to tell which parameters have been passed, especially when "NULL" is a valid value.
CREATE
PROCutlActTypeUpdate
@ActTypeIDTINYINT,
@ActTypeCodeVARCHAR(2)= 'ø',
@ActTypeNameVARCHAR(50)= 'ø',
@ActiveIndTINYINT= 255,
...
Inside the procedure, the UPDATE statement looks at the parameters to determine whether to update the field or not.
UPDATEActType
SETActTypeCode= CASEWHEN @ActTypeCode = 'ø' THEN ActTypeCode ELSE @ActTypeCode END,
ActTypeName= CASEWHEN @ActTypeName = 'ø' THEN ActTypeName ELSE @ActTypeName END,
ActiveInd= CASEWHEN @ActiveInd = 255 THEN ActiveInd ELSE @ActiveInd END,
...
WHEREActTypeID = @ActTypeID
Callers of the procedure must pass the key fields, but then any other parameters/columns are optional. To UPDATE the name only, the following call is made:
EXEC utlActTypeUpdate @ActTypeID = 1, @ActTypeName = 'New Name'
To UPDATE multiple fields, use more parameters:
EXEC utlActTypeUpdate @ActTypeID = 1, @ActTypeName = 'New Name', @ActiveInd = 0
February 21, 2012 at 4:12 am
Jeff Moden (2/19/2012)
Eugene Elutin (2/19/2012)
rajeev_vandakar-782919 (2/18/2012)
It's not totally possible, and anyway you would still better to write normal sql and update all 30-40 columns.Not if you have triggers based on some of the individual columns.
Jeff, you should agree that the question asked in a such :w00t: obscure way that is hard to understand what the OP really wants.
I thought he is asking something alon the following lines:
if stored proc has optional input parameters which will be (can be) used in update query, is it possible to identify which parameters actually were passed into proc by the caller.
The answer to this is: No, there is no 100% guaranteed way to find it out.
Now the question is, what OP does really want?
February 21, 2012 at 4:45 am
Now the question is, what OP does ally want?
My answer to this question is: "Huh?"
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 21, 2012 at 5:14 am
my typing ***...
Post edited! 🙂
February 21, 2012 at 5:23 am
Eugene Elutin (2/21/2012)
my typing ***...Post edited! 🙂
Hmmm, 'corrected' version:
Now the question is, what OP does really want?
I suggest you quit digging :w00t:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 21, 2012 at 6:04 am
February 21, 2012 at 6:40 am
I must apologise - I was being hard on you because I thought you were a native English speaker. But I reread your posts and I don't think you are, so my intended wit is not very funny at all.
Just for information, while the following is perfectly understandable to an English person, it's not how we would write it:
"Now the question is, what OP does really want?"
Instead, we would write:
"Now the question is, what does the OP really want?"
Sorry again.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 21, 2012 at 6:58 am
Just tried to be formal and not payed enough attention...
Could do just with:
"Now the question is, what OP really wants?"
Looks like we can re-name the forum to "English Grammar" :hehe:
I guess it's not hard to find out that I'm not a native English speaker, just check my signature...
😉
February 21, 2012 at 10:05 am
Eugene Elutin (2/21/2012)
Just tried to be formal and not payed enough attention...Could do just with:
"Now the question is, what OP really wants?"
Looks like we can re-name the forum to "English Grammar" :hehe:
I guess it's not hard to find out that I'm not a native English speaker, just check my signature...
😉
BWAA-HAAA!!!! "English" IS my first language but that doesn't stop me from making similar "mistrakes". 😛 I suppose because of my own ineptitude in the written and spoken word, I knew exactly what you meant. 🙂
Phil... go easy on us non-pro English abusers... we do this to help, not to get "A"s in grammer. I know many ESL's that absolutely trash the English language and I'll never call them out on it because I'm too busy learning what they think about SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2012 at 10:20 am
Phil... go easy on us non-pro English abusers... we do this to help, not to get "A"s in grammer. I know many ESL's that absolutely trash the English language and I'll never call them out on it because I'm too busy learning what they think about SQL Server.
'Twas just a bit of fun that backfired. Usually I choose my targets with more care. Database internals can be a dry subject and it's nice sometimes to inject a little humour.
It's also about the only thing I'll ever call people out on in this forum, because I generally know less than them about SQL Server!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply