Binary fields efficiency

Hi there,
I have a record where associated with it are upto 1000 floating point
values, would it be more efficient to have a variable sized binary field to
hold an array of floating point values, or is it fine to create another tabl
e
to hold these floating points required of the records. I'll have thousands
of the records, although they wont be accessed often - ie, once during an
import.
Thanks
K.Create the table. You are using a Relational database, which works
really well when the data is structured relationally. Use the
relational approach and you probably won't have to ask for help here
very often. Use the non-relational approach and you will generally
get a lot of heartburn when SQL Server doesn't have the tools to work
with the data. And when you end up asking for help here, you will
mostly get criticism of your database design!
So create the table. SQL Server handles thousands of rows very
efficiently, and the SQL you will need to manipulate them will be
clear and straightforward.
Roy Harvey
Beacon Falls, CT
On Thu, 2 Mar 2006 02:45:28 -0800, "Katie Viola"
<KatieViola@.discussions.microsoft.com> wrote:

>Hi there,
>I have a record where associated with it are upto 1000 floating point
>values, would it be more efficient to have a variable sized binary field t
o
>hold an array of floating point values, or is it fine to create another tab
le
>to hold these floating points required of the records. I'll have thousands
>of the records, although they wont be accessed often - ie, once during an
>import.
>Thanks
>K.

Binary data into File.

Is there any way to save the image data into one file.... basically I have
one record in a table and I want to save that image data into one separate
file... is that possible ?
What do you mean by file? Are you talking about a database file or a window
file?
Andrew J. Kelly SQL MVP
"msnews.microsoft.com" <Noor> wrote in message
news:%23zXUsBJtFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Is there any way to save the image data into one file.... basically I
> have one record in a table and I want to save that image data into one
> separate file... is that possible ?
>
|||Windows file...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:utBvIlKtFHA.2756@.TK2MSFTNGP09.phx.gbl...
> What do you mean by file? Are you talking about a database file or a
> window file?
> --
> Andrew J. Kelly SQL MVP
>
> "msnews.microsoft.com" <Noor> wrote in message
> news:%23zXUsBJtFHA.3188@.TK2MSFTNGP14.phx.gbl...
>
|||Have you tried using DTS to export it to a file?
Andrew J. Kelly SQL MVP
"msnews.microsoft.com" <Noor> wrote in message
news:O1no8nKtFHA.464@.TK2MSFTNGP15.phx.gbl...
> Windows file...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:utBvIlKtFHA.2756@.TK2MSFTNGP09.phx.gbl...
>

Better proformance using join or sub-query?

All,
I am using a trigger to keep a "transaction date" column up-to-date with
the datetime the record was lasted inserted/updated. My question is which
SQL statement would provide better performance:
Update tablename set transdate = getdate() where primarykey in (select
primarykey from inserted)
or
update tablename set transdate = getdate
from tablename, inserted
where tablename.primarykey = inserted.primarykey
Thanks,
James K.I recommend
update tablename set transdate = getdate
from tablename, inserted
where tablename.primarykey = inserted.primarykey
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> All,
> I am using a trigger to keep a "transaction date" column up-to-date
> with the datetime the record was lasted inserted/updated. My question is
> which SQL statement would provide better performance:
> Update tablename set transdate = getdate() where primarykey in (select
> primarykey from inserted)
> or
> update tablename set transdate = getdate
> from tablename, inserted
> where tablename.primarykey = inserted.primarykey
> Thanks,
> James K.
>|||I think the second one
Madhivanan|||JKL
I'd go with the first one. The probelm with the second one is that under
some conditions you can get a different ( wrong) output
David Portas has written some script/test about this
CREATE TABLE Countries
(countryname VARCHAR(20) NOT NULL PRIMARY KEY,
capitalcity VARCHAR(20));
CREATE TABLE Cities
(cityname VARCHAR(20) NOT NULL,
countryname VARCHAR(20) NOT NULL
REFERENCES Countries (countryname),
CONSTRAINT PK_Cities
PRIMARY KEY (cityname, countryname));
INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
INSERT INTO Cities VALUES ('Washington', 'USA');
INSERT INTO Cities VALUES ('London', 'UK');
INSERT INTO Cities VALUES ('Manchester', 'UK');
The MS-syntax makes it all too easy for the developer to slip-up by
writing ambiguous UPDATE...FROM statements where the JOIN criteria is
not unique on the right side of the join.
Try these two identical UPDATE statements with a small change to the
primary key in between.
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
cityname);
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* don't do this! */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
You get this from the first SELECT statement:
countryname capitalcity
-- --
UK London
USA Washington
and this from the second:
countryname capitalcity
-- --
UK Manchester
USA Washington
"-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> All,
> I am using a trigger to keep a "transaction date" column up-to-date
with
> the datetime the record was lasted inserted/updated. My question is which
> SQL statement would provide better performance:
> Update tablename set transdate = getdate() where primarykey in (select
> primarykey from inserted)
> or
> update tablename set transdate = getdate
> from tablename, inserted
> where tablename.primarykey = inserted.primarykey
> Thanks,
> James K.
>|||Uri,
The original poster want to update the column the getDate().
I think, As long as you dont depend on the joined table for the
value to update, you are safe.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Of9GdAoGFHA.2420@.TK2MSFTNGP14.phx.gbl...
> JKL
> I'd go with the first one. The probelm with the second one is that under
> some conditions you can get a different ( wrong) output
> David Portas has written some script/test about this
> CREATE TABLE Countries
> (countryname VARCHAR(20) NOT NULL PRIMARY KEY,
> capitalcity VARCHAR(20));
> CREATE TABLE Cities
> (cityname VARCHAR(20) NOT NULL,
> countryname VARCHAR(20) NOT NULL
> REFERENCES Countries (countryname),
> CONSTRAINT PK_Cities
> PRIMARY KEY (cityname, countryname));
> INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
> INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
> INSERT INTO Cities VALUES ('Washington', 'USA');
> INSERT INTO Cities VALUES ('London', 'UK');
> INSERT INTO Cities VALUES ('Manchester', 'UK');
> The MS-syntax makes it all too easy for the developer to slip-up by
> writing ambiguous UPDATE...FROM statements where the JOIN criteria is
> not unique on the right side of the join.
> Try these two identical UPDATE statements with a small change to the
> primary key in between.
> UPDATE Countries
> SET capitalcity = cityname
> FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
> ON Countries.countryname = Cities.countryname;
> SELECT * FROM Countries;
> ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
> ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
> cityname);
> UPDATE Countries
> SET capitalcity = cityname
> FROM Countries JOIN Cities /* don't do this! */
> ON Countries.countryname = Cities.countryname;
> SELECT * FROM Countries;
> You get this from the first SELECT statement:
> countryname capitalcity
> -- --
> UK London
> USA Washington
> and this from the second:
> countryname capitalcity
> -- --
> UK Manchester
> USA Washington
>
>
> "-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
> news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> with
>|||On Fri, 25 Feb 2005 13:00:35 +0530, avnrao wrote:

>Umi, can you please explain the rationale behind it.
Hi Av.,
Mind if I do instead of Uri?
The ANSI syntax and the proprietary UPDATE FROM syntax behave only the
same if for each row in the updated table that satisfies the WHERE
clause, exactly one row in the other table matches the join criteria.
If it's possible that no rows are matched, the ANSI syntax will set the
column to NULL, unless excluded from the update in the WHERE clause. The
UPDATE FROM syntax will not update this row: since it doesn't satisfy
the join criteria, it won't be updated even if it does meet the
requirements of the WHERE clause.
(Note: this difference can be circumvened by using outer join instead of
inner join to make the joined update behave exactly as the ANSI version)
The biggest difference is when one row to be updated can be matched
against more than one row in the source table. This is what's happening
in David's script, as posted by Uri.
In the ANSI version, if the subquery in the SET clause returns more than
one row, you'll get an error message. That ensures that you will revisit
the query and change it so that it will always return exactly one row -
the row that you want it to return.
The UPDATE FROM syntax won't throw an error if a row in the table to be
updated matches against multiple rows from the source. Instead, SQL
Server will just pick one of these rows and use that one to determine
the new values in your table. Or rather, in case you want the full
details, it will update the same row over and over again with each
matching row - and since the result of all but the last change is
overwritten, the net effect is that only the results from the row
processed last will stick.
The above explains why the result of an UPDATE FROM where one row to be
updated matches more than one row from the source is completely
unpredictable: the result will be from the last row processed, but there
is no way to predict the order of evaluation SQL Server will choose for
your query. David's example shows how an index can change the order, but
there are other possibilities: parallellism, available memory, workload,
and the possibility to "piggy-back" another query are just some examples
of how the order of evaluation (and hence the result of the update) may
change between successive execution, even if no schema or data has been
changed!
I hope this helps.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Best way to track who is accessing a record

I have an application that has a SQL back end, and I want to be able to track who is accessing a record so that no one else can access it at the same time. I was going to do this with a table or application state, but how can I avoid keeping files locked when someone abandons a session? Any Ideas?There are third party tools to do it but try the links below for code to track updates and links to the third party tools. Hope this helps.
http://www.aspfaq.com/show.asp?id=2448

best way to store simple up or down values

I'm creating a table for maintenance records.

In each record, many of the values are simply checkboxes (on the UI).

In the database table for these attributes, is a good way to store the
state of these checkboxes as simple as 0 for false, 1 for true?

-David"wireless" <wireless200@.yahoo.com> wrote in message
news:90446ee7.0402110709.30e1e084@.posting.google.c om...
> I'm creating a table for maintenance records.
> In each record, many of the values are simply checkboxes (on the UI).
> In the database table for these attributes, is a good way to store the
> state of these checkboxes as simple as 0 for false, 1 for true?
> -David

There are two common ways to do this - either use a bit column, or use
something like a char(1) with a check constraint to ensure the values are
T/F or Y/N. The second solution is more portable, if that's a concern.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> There are two common ways to do this - either use a bit column, or use
> something like a char(1) with a check constraint to ensure the values are
> T/F or Y/N. The second solution is more portable, if that's a concern.

On the other hand it is more sensitive to localization issues. We used
to have such columns in our databases, but I think all are gone now. The
values we used where J/N.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns948CEE4DEAA00Yazorman@.127.0.0.1>...
> Simon Hayes (sql@.hayes.ch) writes:
> > There are two common ways to do this - either use a bit column, or use
> > something like a char(1) with a check constraint to ensure the values are
> > T/F or Y/N. The second solution is more portable, if that's a concern.
> On the other hand it is more sensitive to localization issues. We used
> to have such columns in our databases, but I think all are gone now. The
> values we used where J/N.

That's a good point, although I would guess (very possibly
incorrectly) that many IT people would be familiar with 'True' and
'False' as Boolean values in various programming languages, even if
their own natural language isn't English. Personally, I think a bit is
the most obvious data type for flags, but then that seems to invite a
lecture from Joe Celko...

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<402a789f$1_3@.news.bluewin.ch>...

> There are two common ways to do this - either use a bit column, or use
> something like a char(1) with a check constraint to ensure the values are
> T/F or Y/N. The second solution is more portable, if that's a concern.

Thanks, I went with the bit method. I've been working on projects
involving Oracle and other databases for the past few months and hope
not to port anything to any of them! I'm content to be back to a SQL
Server db.

-David|||Simon Hayes (sql@.hayes.ch) writes:
> That's a good point, although I would guess (very possibly
> incorrectly) that many IT people would be familiar with 'True' and
> 'False' as Boolean values in various programming languages, even if
> their own natural language isn't English. Personally, I think a bit is
> the most obvious data type for flags, but then that seems to invite a
> lecture from Joe Celko...

Already when you give the choice of Y/N and T/F you have give a choice
that can be source for confusion. One of the DBA goes for the former,
another for the latter, guess if developers will mess up.

As for localisation, recall that some of this data may make to a GUI.
That was the case with our J/N, which our Swedish users had no problem to
understand. They might be able to make out Y/N too, but I would guess that
T/F, or even S/F, would leave them completely in the dark.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I'm creating a table for maintenance records. In each record, many
of the values are simply checkboxes (on the UI). <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

When you convert the *record* on the paper forms into one or more
*rows* in one or more tables in the database, what does the data look
like?

What I have seen is for maintenance databases is that "yes/no" is not
good enough. You need to know temporal information for each task,
like "scheduled time" and "completed time" as minimal data for
computing MTBF and other things. Would you write an accountign system
in which you had flag for "paid/not paid" and leave out the amounts
and dates?

Simple yes/no flags are all too often computed columns that can be
deduced from the other attributes in the database.

Best Way to handle UID to another server

I have a need when a Update, Insert or Delete is done to a record in DB "A", it will send the appropriate UID to a different table in different DB "B".

My first thought was to have a trigger on the table in DB "A" simply call a stored procedure on DB "B" and do the UID.

However - my question is what is the best approach and what's the best way to establish the connection to DB "B" for the UID from within DB "A"? We can't use linked servers - DNSLESS string would be the preferred connect way. Not sure how to execute it within a trigger.

Is even using a Trigger to Stored Proc the best way?

What about Transaction Replication - which I've never attempted - is that a better way?

Just looking for some guidance here so I don't needlessly burn time down a path that isn't recommended or simply won't work.

Thanks as always for your input,

PeterI would never suggest using triggers that reference objects outside of their own database. A recipe for disaster.
A better strategy would be to have the trigger load your data into a staging table, and then have a job scheduled once per minute to transfer the data from the staging table to the remote server. This is very robust if you can handle latency of up to one minute (scheduled jobs can be set to run once per minute).
If you require zero latency, then I would look into replication as a solution.|||Thanks Blindman:

I did some BOL checking and replication sounds good, but the table the data needs to go into has different field names and is in use in another application. The target DB happens to be on the same server.

What about have a trigger call a stored procedure and pass the fields in question along with the key to the SP?

The SP would make the connection to the other DB and do the UID.

You remain helpful,

Peter|||I still don't like it. What you are trying to avoid is having your trigger fail and system "A" come to a crashing halt when system "B" goes down or the connection fails. Your solution needs to be strong enough to fail safely, or you are just doubling or trippling the possible points of failure.
The advantage of the staging tables is that, if the connection to system "B" is lost then system "A" continues merrily on, loading data into the staging tables. When system "B" comes back online the data in the staging tables is automatically transferred.
No downtown for system "A".
No data-loss for system "B".
No late-night hours getting both systems back in synch for "DBA".
If you can stand up to a 1 minute latency, this is the way to go.
If you can't handle the latency, then replicate to duplicate tables in system "B" and put the triggers on THEM.|||Blindman:

I can stand a latency of even up to 5 minutes.

My concerns:

1. The table I'm feeding updates to has a different name, naming scheme and field types. I'll need to manipulate the data I'm "pushing" to Server B regardless of the approach I take.
2. With #1, I didn't know if Replication can handle. Perhaps "Transactional Replication" isn't the proper type of replication for this
3. I'm totally new to Replication and it doesn't look trivial via BOL.

Any additional help here is appreciated.

I'm event considering having the application update the server B tables directly. I don't like that for a number of reasons including if someone updates the back-end directly (unlikely - but possible), it wouldn't be updating server B.

Peter|||The staging table method should handle all your concerns. I would recommend transforming the data when it is shuttled from the staging tables to the destination database, rather than when it is inserted into the staging tables, as you want your triggers to be as streamlined as possible.

Best Way to delete a parent record

whats the best way to delete a parent record?

for now what i am doing is to have a special delete page (means not DetailsView/GridView) just to delete parent records so user can see what child records will be deleted too. is the a better way?

my code below:

The T-SQL Stored Procedure

ALTER PROCEDURE dbo.DeleteInventory(@.InventoryIDint)ASDELETE FROM BookingWHERE InventoryID = @.InventoryIDDELETE FROM InventoryWHERE InventoryID = @.InventoryID

The code in the Code Behind to execute when a button(delete) click

Try Dim tempAs Integer = SqlDataSource4.DeleteIf temp > 0Then lblDeleteDtatus.ForeColor = Drawing.Color.Blue lblDeleteDtatus.Text = temp &" Records/Rows Deleted."Else lblDeleteDtatus.ForeColor = Drawing.Color.Orange lblDeleteDtatus.Text ="No records Deleted!"End If Catch exAs Exception lblDeleteDtatus.ForeColor = Drawing.Color.Red lblDeleteDtatus.Text ="An Error Occured.<br />" & ex.Message.ToStringEnd Try

any better methods anyone wants to share?

Assuming that it makes business sense to delete an inventory record and orders for that item, your stored procedure would be better as

ALTER PROCEDURE dbo.DeleteInventory
(
@.InventoryID int,
@.Count INT OUTPUT -- Return the number of Inventory records to delete as an output parameter
)
AS
SET NOCOUNT ON
SELECT @.COUNT = COUNT(*) FROM Booking WHERE InventoryID = @.InventoryID
BEGIN TRANSACTION
DELETE FROM Booking WHERE InventoryID = @.InventoryID
DELETE FROM Inventory WHERE InventoryID = @.InventoryID
COMMIT

|||

i used "

Dim

"tempAs Integer = SqlDataSource4.Delete"

to get the rows affected, isn't it about the same? by the way with ur method, its simpler but
i seem to have problems. how do i "link" it to a variable? i know how to link it to a Control, etc but how to a variable?

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
>