insert/update NULL instead of ''

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,
RainerRainer,
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
>|||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...
>|||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 th
e
procedures cannot be done (i.e. if there aren't any).
Or did I miss something?
ML|||Functionality wise...a trigger should work fine.
HTH
Jerry
"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...
>|||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|||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
"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!
ML|||Or as Cosmo would say: Wonderbro! ;-)
Jerry
"ML" <ML@.discussions.microsoft.com> wrote in message
news:1D8DEB5B-C763-4B1F-A18E-2E87411B674A@.microsoft.com...
> As the modern German would say: Wonderbra!
>
> ML|||Hmm... which one? :)
http://en.wikipedia.org/wiki/Cosmo
MLsql