|
|
You're in the
sql server programming
group:insert/update NULL instead of ''
sql server programming:
Rainer,
Why not just evaluate the incoming value (i.e., stored procedure parameter) if the value IS NULL replace it with ''. You could optionally use a trigger as well but the former would probably give better performance and should probably be used unless you can't control the input method/application. HTH Jerry [quoted text, click to view] "Rainer Ebert" <rainer_ebert_at_arcor.de> wrote in message news:%23127HJC1FHA.2884@TK2MSFTNGP09.phx.gbl... > Hi, > > due to special reasons I have to ensure, that in insert and update > statements for varchar-columns (which allow NULL-values) the value '' > automatically becomes replaced by NULL before the records have been > inserted/updated. (This because I have to convert a large application from > another database -which automatically substituted '' by NULL- to SqlServer > 2000 SP4). > > My first thought was to find a setting in SqlServer server. But I didn't > found one. Is there any? > > My second thought was to find a setting in the OLEDB-provider. But I > didn't found one. Is there any? > (I'm using OLEDB, but not ADO) > > My third thought has been to define triggers for that case (my very first > ones). I did it as listed below. Is this the correct way? Or is there a > better way? Or perhaps a way with better performance? > > create table test (primkey integer not null, testcol1 varchar(10), > testcol2 varchar(10)) > > create trigger test_trigger_u on test instead of update > as > update test set primkey = i.primkey, > testcol1 = nullif(i.testcol1, ''), > testcol2 = nullif(i.testcol2, '') > from test t inner join inserted i > on t.primkey = i.primkey > > create trigger test_trigger_i on test instead of insert > as > insert into test (primkey, > testcol1, > testcol) > select primkey, > nullif(testcol1, ''), > nullif(testcol2, '') > from inserted > > Regards, > Rainer >
I believe Rainer is actually looking for a way to *insert* null values
instead of the supplied empty strings rather than trying to prevent null values from being inserted. NULLIF is the way to go. Yet, I'd suggest handling that in the insert procedures, rather than in the triggers, and use the triggers if changing the procedures cannot be done (i.e. if there aren't any). Or did I miss something?
Functionality wise...a trigger should work fine.
HTH Jerry [quoted text, click to view] "Rainer Ebert" <rainer_ebert_at_arcor.de> wrote in message news:uzfHiaC1FHA.904@tk2msftngp13.phx.gbl... > Jerry, > > our application does not use stored procedures. It uses sql-statements to > select, insert, update and delete data. The application should run against > the previous database (Gupta SQLBase) and against MS SqlServer (depending > on the customer). This goal should be reached with as few source code > modifications as possible. I know, that I can change each affected > insert/update statement in the sourcecode and replace '' by NULL. But I'm > looking for a way to avoid doing this. > > Do you think, the triggers are o.k.? > > Do you know a better way? > > regards, > Rainer > > P.S.: By the way, I want to replaye '' by NULL, not NULL by '' > > "Jerry Spivey" <jspivey@vestas-awt.com> schrieb im Newsbeitrag > news:ObsNBRC1FHA.3124@TK2MSFTNGP12.phx.gbl... >> Rainer, >> >> Why not just evaluate the incoming value (i.e., stored procedure >> parameter) if the value IS NULL replace it with ''. You could optionally >> use a trigger as well but the former would probably give better >> performance and should probably be used unless you can't control the >> input method/application. >> >> HTH >> >> Jerry >> "Rainer Ebert" <rainer_ebert_at_arcor.de> wrote in message >> news:%23127HJC1FHA.2884@TK2MSFTNGP09.phx.gbl... >>> Hi, >>> >>> due to special reasons I have to ensure, that in insert and update >>> statements for varchar-columns (which allow NULL-values) the value '' >>> automatically becomes replaced by NULL before the records have been >>> inserted/updated. (This because I have to convert a large application >>> from another database -which automatically substituted '' by NULL- to >>> SqlServer 2000 SP4). >>> >>> My first thought was to find a setting in SqlServer server. But I didn't >>> found one. Is there any? >>> >>> My second thought was to find a setting in the OLEDB-provider. But I >>> didn't found one. Is there any? >>> (I'm using OLEDB, but not ADO) >>> >>> My third thought has been to define triggers for that case (my very >>> first ones). I did it as listed below. Is this the correct way? Or is >>> there a better way? Or perhaps a way with better performance? >>> >>> create table test (primkey integer not null, testcol1 varchar(10), >>> testcol2 varchar(10)) >>> >>> create trigger test_trigger_u on test instead of update >>> as >>> update test set primkey = i.primkey, >>> testcol1 = nullif(i.testcol1, ''), >>> testcol2 = nullif(i.testcol2, '') >>> from test t inner join inserted i >>> on t.primkey = i.primkey >>> >>> create trigger test_trigger_i on test instead of insert >>> as >>> insert into test (primkey, >>> testcol1, >>> testcol) >>> select primkey, >>> nullif(testcol1, ''), >>> nullif(testcol2, '') >>> from inserted >>> >>> Regards, >>> Rainer >>> >> >> > >
I believe Rainer is actually looking for a way to *insert* null values
instead of the supplied empty strings rather than trying to prevent null values from being inserted. NULLIF is the way to go. Yet, I'd suggest handling that in the insert procedures, rather than in the triggers, and only use triggers if changing the procedures is not an option (i.e. if - for some insane reason - there aren't any). Or am I missing something?
Ahh...ML...ok.
Something like: CREATE TABLE #TEST (ID INT NOT NULL, VAL VARCHAR(10)) DECLARE @VAL VARCHAR(10) SET @VAL = '' INSERT #TEST(ID,VAL) VALUES(1,NULLIF(@VAL,'')) SELECT * FROM #TEST --DROP TABLE #TEST then? HTH Jerry [quoted text, click to view] "ML" <ML@discussions.microsoft.com> wrote in message news:D577E511-2347-4A2B-9F26-FA578CFEF71B@microsoft.com... >I believe Rainer is actually looking for a way to *insert* null values > instead of the supplied empty strings rather than trying to prevent null > values from being inserted. > > NULLIF is the way to go. Yet, I'd suggest handling that in the insert > procedures, rather than in the triggers, and only use triggers if changing > the procedures is not an option (i.e. if - for some insane reason - there > aren't any). > > Or am I missing something? > > > ML
As the modern German would say: Wonderbra!
Or as Cosmo would say: Wonderbro! ;-)
Jerry [quoted text, click to view] "ML" <ML@discussions.microsoft.com> wrote in message news:1D8DEB5B-C763-4B1F-A18E-2E87411B674A@microsoft.com... > As the modern German would say: Wonderbra! > > > ML
Hi,
due to special reasons I have to ensure, that in insert and update statements for varchar-columns (which allow NULL-values) the value '' automatically becomes replaced by NULL before the records have been inserted/updated. (This because I have to convert a large application from another database -which automatically substituted '' by NULL- to SqlServer 2000 SP4). My first thought was to find a setting in SqlServer server. But I didn't found one. Is there any? My second thought was to find a setting in the OLEDB-provider. But I didn't found one. Is there any? (I'm using OLEDB, but not ADO) My third thought has been to define triggers for that case (my very first ones). I did it as listed below. Is this the correct way? Or is there a better way? Or perhaps a way with better performance? create table test (primkey integer not null, testcol1 varchar(10), testcol2 varchar(10)) create trigger test_trigger_u on test instead of update as update test set primkey = i.primkey, testcol1 = nullif(i.testcol1, ''), testcol2 = nullif(i.testcol2, '') from test t inner join inserted i on t.primkey = i.primkey create trigger test_trigger_i on test instead of insert as insert into test (primkey, testcol1, testcol) select primkey, nullif(testcol1, ''), nullif(testcol2, '') from inserted Regards, Rainer
Jerry,
our application does not use stored procedures. It uses sql-statements to select, insert, update and delete data. The application should run against the previous database (Gupta SQLBase) and against MS SqlServer (depending on the customer). This goal should be reached with as few source code modifications as possible. I know, that I can change each affected insert/update statement in the sourcecode and replace '' by NULL. But I'm looking for a way to avoid doing this. Do you think, the triggers are o.k.? Do you know a better way? regards, Rainer P.S.: By the way, I want to replaye '' by NULL, not NULL by '' "Jerry Spivey" <jspivey@vestas-awt.com> schrieb im Newsbeitrag news:ObsNBRC1FHA.3124@TK2MSFTNGP12.phx.gbl... [quoted text, click to view] > Rainer, > > Why not just evaluate the incoming value (i.e., stored procedure > parameter) if the value IS NULL replace it with ''. You could optionally > use a trigger as well but the former would probably give better > performance and should probably be used unless you can't control the input > method/application. > > HTH > > Jerry > "Rainer Ebert" <rainer_ebert_at_arcor.de> wrote in message > news:%23127HJC1FHA.2884@TK2MSFTNGP09.phx.gbl... >> Hi, >> >> due to special reasons I have to ensure, that in insert and update >> statements for varchar-columns (which allow NULL-values) the value '' >> automatically becomes replaced by NULL before the records have been >> inserted/updated. (This because I have to convert a large application >> from another database -which automatically substituted '' by NULL- to >> SqlServer 2000 SP4). >> >> My first thought was to find a setting in SqlServer server. But I didn't >> found one. Is there any? >> >> My second thought was to find a setting in the OLEDB-provider. But I >> didn't found one. Is there any? >> (I'm using OLEDB, but not ADO) >> >> My third thought has been to define triggers for that case (my very first >> ones). I did it as listed below. Is this the correct way? Or is there a >> better way? Or perhaps a way with better performance? >> >> create table test (primkey integer not null, testcol1 varchar(10), >> testcol2 varchar(10)) >> >> create trigger test_trigger_u on test instead of update >> as >> update test set primkey = i.primkey, >> testcol1 = nullif(i.testcol1, ''), >> testcol2 = nullif(i.testcol2, '') >> from test t inner join inserted i >> on t.primkey = i.primkey >> >> create trigger test_trigger_i on test instead of insert >> as >> insert into test (primkey, >> testcol1, >> testcol) >> select primkey, >> nullif(testcol1, ''), >> nullif(testcol2, '') >> from inserted >> >> Regards, >> Rainer >> > >
What else would a male brassierre be called. :)
Don't know...don't want to know!!! ;-)
[quoted text, click to view] "ML" <ML@discussions.microsoft.com> wrote in message news:7FB0508F-80C1-4A95-90D6-CC79063B7397@microsoft.com... > What else would a male brassierre be called. :) > > > ML
Stay away from women's skin-care products, then. :)
On the other hand, what were we originally discussing? Oh, yes. Controlling inserts and updates. Getting the null values into nullable columns. I guess not setting them at all might do the trick. :)
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |