Best way to insert a DateModified in updates

how do i update the date modified Field in my DB. i was thinking of update trigger? i am using ASP 2 with DetailsView for Editing(Updates in SQL term). i can make a Update SP but think that might not be needed

If you use the SP you can define the date now to a variable. @.datenow = GetDate()|||

In the Update procedure, you can just "SET dateModified=GETDATE()".

In my view, Triggers are a little bad in the respect that they are hidden. If you look at a simple SQL statement, you really can't "trace" the code execution completely if your DB has a whole pile of Triggers firing all the time, which means, things might be happening that the SQL statement writer isn't aware of, and that can cause problems.

Of course, their not overall bad, but my personal preference is to use an SP, unless a Trigger is specifically required or for some odd reason more effecient.

Best way for Stored Procedure to update fields conditionally?

I want to write a stored procedure that updates a record, only updating
the fields where the value passed is not null.
So ideally in the UPDATE statement I want something like
CREATE PROCEDURE myproc
@.param1 nvarchar(20),
@.param2 nvarchar(20)
UPDATE myrec
SET
IF @.param1 IS NOT null
param1=@.param1,
IF @.param2 IS NOT null
param1=@.param2
etc.
but I'm guessing I can't do that.
Obviously I don't want to have to read the record first to compare the
current values with the ones I'm passing in.
What's the best (shortest, most efficient) way to do this?
Thanks,
ChrisNHi,
what about set Col1 = COALESCE(@.Param1, Col1)
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||First off I would ask why you care? If you are going to update the row the
extra overhead to update columns that don't need to is extremely small and
trivial compared to the other operations that must occur for an update.
Second how can you check for NULL? Are you saying none of your columns
allow NULL's? If so how would you pass in a NULL?
--
Andrew J. Kelly SQL MVP
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegroups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>|||If you want to update based on the values passed in, you can do something
such as this
IF @.param1 IS NOT NULL
BEGIN
UPDATE tabel
SET column1 = @.param1
WHERE ...
END
IF @.param2 IS NOT NULL
BEGIN
UPDATE table
SET column2 = @.param2
WHERE ..
END
Or you could split it up into multiple stored proceduces.
Why you would want to do this, I am not sure...
Keep in mind that when using IF conditions in a stored procedure, SQL is
less likely to re-use execution plans, lessening the benefit of using a
stored procedure.
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegroups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>

Best way for Stored Procedure to update fields conditionally?

I want to write a stored procedure that updates a record, only updating
the fields where the value passed is not null.
So ideally in the UPDATE statement I want something like
CREATE PROCEDURE myproc
@.param1 nvarchar(20),
@.param2 nvarchar(20)
UPDATE myrec
SET
IF @.param1 IS NOT null
param1=@.param1,
IF @.param2 IS NOT null
param1=@.param2
etc.
but I'm guessing I can't do that.
Obviously I don't want to have to read the record first to compare the
current values with the ones I'm passing in.
What's the best (shortest, most efficient) way to do this?
Thanks,
ChrisNHi,
what about set Col1 = COALESCE(@.Param1, Col1)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||First off I would ask why you care? If you are going to update the row the
extra overhead to update columns that don't need to is extremely small and
trivial compared to the other operations that must occur for an update.
Second how can you check for NULL? Are you saying none of your columns
allow NULL's? If so how would you pass in a NULL?
Andrew J. Kelly SQL MVP
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegroups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>|||If you want to update based on the values passed in, you can do something
such as this
IF @.param1 IS NOT NULL
BEGIN
UPDATE tabel
SET column1 = @.param1
WHERE ...
END
IF @.param2 IS NOT NULL
BEGIN
UPDATE table
SET column2 = @.param2
WHERE ..
END
Or you could split it up into multiple stored proceduces.
Why you would want to do this, I am not sure...
Keep in mind that when using IF conditions in a stored procedure, SQL is
less likely to re-use execution plans, lessening the benefit of using a
stored procedure.
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegroups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>

Best way for Stored Procedure to update fields conditionally?

I want to write a stored procedure that updates a record, only updating
the fields where the value passed is not null.
So ideally in the UPDATE statement I want something like
CREATE PROCEDURE myproc
@.param1 nvarchar(20),
@.param2 nvarchar(20)
UPDATE myrec
SET
IF @.param1 IS NOT null
param1=@.param1,
IF @.param2 IS NOT null
param1=@.param2
etc.
but I'm guessing I can't do that.
Obviously I don't want to have to read the record first to compare the
current values with the ones I'm passing in.
What's the best (shortest, most efficient) way to do this?
Thanks,
ChrisN
Hi,
what about set Col1 = COALESCE(@.Param1, Col1)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||First off I would ask why you care? If you are going to update the row the
extra overhead to update columns that don't need to is extremely small and
trivial compared to the other operations that must occur for an update.
Second how can you check for NULL? Are you saying none of your columns
allow NULL's? If so how would you pass in a NULL?
Andrew J. Kelly SQL MVP
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegro ups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>
|||If you want to update based on the values passed in, you can do something
such as this
IF @.param1 IS NOT NULL
BEGIN
UPDATE tabel
SET column1 = @.param1
WHERE ...
END
IF @.param2 IS NOT NULL
BEGIN
UPDATE table
SET column2 = @.param2
WHERE ..
END
Or you could split it up into multiple stored proceduces.
Why you would want to do this, I am not sure...
Keep in mind that when using IF conditions in a stored procedure, SQL is
less likely to re-use execution plans, lessening the benefit of using a
stored procedure.
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegro ups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>