February 21, 2012 at 10:27 am
Phil Parkin (2/21/2012)
..
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!
...
Don't be so harsh to yourself 🙂
I don't mind jokes about my English!
Keep it SIMPLES!
February 21, 2012 at 2:11 pm
fahey.jonathan (2/20/2012)
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.
You can avoid having "magic" values quite easily, by having just one extra bigint parameter (assuming you have fewer than 64 columns). If that parameter is called @validcols theese are the changes to teh code you suggested
UPDATEActType
SETActTypeCode= CASEWHEN @ActTypeCode = 'ø'@validcols&1 = 0 THEN ActTypeCode ELSE @ActTypeCode END,
ActTypeName= CASEWHEN @ActTypeName = 'ø'@validcols&2 = 0 THEN ActTypeName ELSE @ActTypeName END,
ActiveInd= CASEWHEN @ActiveInd = 255@validcols&4 = 0 THEN ActiveInd ELSE @ActiveInd END,
...
WHEREActTypeID = @ActTypeID
Of course, this (and your version with magic codes) fails on the point Jeff raised about triggers - but if you've avoided triggers that care whether a value is "changed" to the same value as it had before that's not a problem. (I hate triggers that use COLUMNS_CHANGED() to take special action when a column is set to the value it already had, but some people think that sort of thing is useful.)
Tom
February 21, 2012 at 2:22 pm
Jeff Moden (2/21/2012)
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.
Actually Jeff, I know many EFL (F for First, not fourth or fifth) who absolutely trash the English language (not you, despite your remark). I sometimes do it myself, as well. And I didn't even notice Eugene's first error until Phil's joke (but then the second one was obvious, once the first had been highlighted).
Tom
February 21, 2012 at 2:27 pm
Eugene Elutin (2/21/2012)
I guess it's not hard to find out that I'm not a native English speaker, just check my signature...😉
Signatures sometimes don't work for those purposes; which (if any) of the languages currently in my signature (or in this post) would you take to be a native language of mine? :alien:
I guess your sig is Russian, but not sure. Is that "duh" at the end "spirit" (???)?
Tom
February 21, 2012 at 5:13 pm
L' Eomot Inversé (2/21/2012)
... Is that "duh" at the end "spirit" (???)?
It is exactly.
The line I've used (with a bit of relevant change :hehe:), is from very well known in Russian educated word Pushkin's five-liner which was used for decades in TV program dedicated to the science, sorry the best English translation I've found is without any rhythm:
Oh, how many of wonderful discoveries
Enlightenment spirit prepares for us
And Experience, the son of difficult errors,
And the Genius, the friend of paradoxes,
And the Case, the God is inventor.
If you like good poetry, there are some of his things here: http://lib.ru/LITRA/PUSHKIN/ENGLISH/kneller01.txt
Much better night read than any BoL 😀
February 22, 2012 at 1:37 am
Phil Parkin (2/21/2012)
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!
I think you grossly underestimate your SQL Server skills, ol' friend. And I should have recognized your good humor except that I'm too bloody tired, lately. For example, I just finished up a critical bit of code here at 3:30AM. Alarm goes off at 5:30 because my sweetie has to go to work quite early. If I'm lucky, I get to go back to sleep until 7:30AM.
Ironically, I hang around here for the same reasons as you except vice versa... I'm pretty good at SQL and know much less about the English language than most of the folks here. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2012 at 3:12 am
Thanks Jeff, I appreciate the genuine tone of your message. And I sympathise with the sleep deprivation - sense of humour is often one of the first casualties. I'm also aware that UK humour does not always come naturally to our over-the-Atlantic friends:-)
Get some sleep sir, look after your health. Your SSC public needs you in top shape.
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 22, 2012 at 8:53 am
To allow for nulls we try to emulate the ISNULL functionality with custom ISNULL functions by datatype and set the Parameter in the sp to NULL. To null numeric and date fields we set the parameter to the maximum (usually the .net equivalent) or a defined constant value outside the normal range of accepted values.
These constants are defined in the application code and we refer to them by their name instead of value. For strings we use a GUID constant. Here's the
Int32 example:
CREATE FUNCTION [dbo].[udf_IsDBNullInt32] ( @userValue AS INT, @columnValue AS INT )
RETURNS INT
AS
BEGIN
DECLARE @retVal INT
SET @retVal = ISNULL(NullIf(userValue,2147483647),@columnValue)
RETURN @retVal
Then in the Update Stored Proc definition you default the parameter
to null:
CREATE PROCEDURE [dbo].[usp_UpdateTable]
@totalWeeks int = NULL,
...
UPDATE SomeTable
SET TotalWeeks = [dbo].[udf_IsDBNullInt32](@totalWeeks,TotalWeeks)
...
This pattern may still cause issues with execution plans
for stored procedures where there are varying parameters being
passed. Specifically if you have varying params for the WHERE clause. We had to use the WITH RECOMPILE clause on the atypical queries if we knew that the updated records could vary considerable (update according to a specific ID, by range, date, etc).
"There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply