Another 101 question

With a plain select if no rows are returned then the row count is 0, but
with a select that assigns to a variable you will get a rowcount of one even
though no actual row was found. I am guessing that is because of assigning
to a variable SQL will return an empty result set, which is a result set of
1, is this correct?
i.e. SET @.mycol =
(SELECT col FROM myTable WHERE id = 1)
Under these circumstances is the best technique to just test the variable
@.mycol for a null value
OR write the select like:
IF EXISTS
(SELECT mycol FROM myTable
WHERE myID = 3)
BEGIN
SET @.mycol =
(SELECT mycol FROM myTable
WHERE myID = 3)
PRINT '@.mycol : ' + CAST(mycol as varchar(15))
END
ELSE
PRINT 'ROW DOES NOT EXIST'
Or is there an even better and/or more professional way to do it?Thank you! I was making my self nuts with the what if's, it seemed that
going beyond validating the current entry could turn into a never ending
task. :)
Except for the news groups I am learning in a vacuum, it is not like
learning in maintenance of a production environment where you get to see
what others have done.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eb6rNDKvFHA.3896@.TK2MSFTNGP15.phx.gbl...
> Thanks Erland, I really should have mentioned that.
> Dazed,
> If you have a Unique constraint you should not have to test for
> duplicates when selecting the values out. The constraint will make sure
> there are no duplicates in the first place.
> --
> Andrew J. Kelly SQL MVP
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96D61468ABF3Yazorman@.127.0.0.1...
>|||What is the desired behavior? DO you simply want to know if one or more
rows exist or not? If so then always use EXISTS.
Andrew J. Kelly SQL MVP
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:eXq$4VHvFHA.2072@.TK2MSFTNGP14.phx.gbl...
> With a plain select if no rows are returned then the row count is 0, but
> with a select that assigns to a variable you will get a rowcount of one
> even though no actual row was found. I am guessing that is because of
> assigning to a variable SQL will return an empty result set, which is a
> result set of 1, is this correct?
> i.e. SET @.mycol =
> (SELECT col FROM myTable WHERE id = 1)
> Under these circumstances is the best technique to just test the variable
> @.mycol for a null value
> OR write the select like:
> IF EXISTS
> (SELECT mycol FROM myTable
> WHERE myID = 3)
> BEGIN
> SET @.mycol =
> (SELECT mycol FROM myTable
> WHERE myID = 3)
> PRINT '@.mycol : ' + CAST(mycol as varchar(15))
> END
> ELSE
> PRINT 'ROW DOES NOT EXIST'
> Or is there an even better and/or more professional way to do it?
>|||When you use a scalar subquery, you can get a one-row, one-column table
that is converted to a scalar; you can get an empty table that is
converted to a NULL; you can get a multi-row, one-column table that
gives a cardinality error when you try to put it into a scalar.|||In this paticular situation I want the field value. I was wondering if it
was better to just do the select and test the variable for a null or do a
EXISTS SELECT and then if it does exist select into the variable.
I'm just learning and trying to find the best way to build a mouse trap,
I've found this news group very helpful. i.e. I had a 130 line procedure
yesterday that was cut down to about 30 from information obtained from the
group. There are a lot of ways to get things to work, some are a lot better
than others. I'm looking for the right ways.
Thank you!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ObA0rlHvFHA.2504@.TK2MSFTNGP15.phx.gbl...
> What is the desired behavior? DO you simply want to know if one or more
> rows exist or not? If so then always use EXISTS.
> --
> Andrew J. Kelly SQL MVP
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:eXq$4VHvFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||OK then there is no need for an EXISTS first. You can do this several ways
like:
SET @.mycol = (SELECT col FROM myTable WHERE id = 1)
or
SELECT @.mycol = Col FROM myTable WHERE id = 1
IF @.myCol IS NOT NULL
BEGIN
-- Do your thing here
END
ELSE
...
Also make sure that there will only be at most 1 row returned. As long as
ID is a unique value you should be ok.
Andrew J. Kelly SQL MVP
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uQq5RzHvFHA.3548@.tk2msftngp13.phx.gbl...
> In this paticular situation I want the field value. I was wondering if it
> was better to just do the select and test the variable for a null or do a
> EXISTS SELECT and then if it does exist select into the variable.
> I'm just learning and trying to find the best way to build a mouse trap,
> I've found this news group very helpful. i.e. I had a 130 line procedure
> yesterday that was cut down to about 30 from information obtained from the
> group. There are a lot of ways to get things to work, some are a lot
> better than others. I'm looking for the right ways.
> Thank you!!
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ObA0rlHvFHA.2504@.TK2MSFTNGP15.phx.gbl...
>|||Thank you again! id is unique, but I am checking if rowcount is > 1 anyways
and plan on throwing an error if it is. I plan on temporarily removing the
unique constraint and putting in a dupe record to test it.
Is that going too far?
Or is it a good idea to try to handle a situation that in theory should
never happen?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMkfpNIvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> OK then there is no need for an EXISTS first. You can do this several
> ways like:
> SET @.mycol = (SELECT col FROM myTable WHERE id = 1)
> or
> SELECT @.mycol = Col FROM myTable WHERE id = 1
>
> IF @.myCol IS NOT NULL
> BEGIN
> -- Do your thing here
> END
> ELSE
> ...
> Also make sure that there will only be at most 1 row returned. As long as
> ID is a unique value you should be ok.
> --
> Andrew J. Kelly SQL MVP
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:uQq5RzHvFHA.3548@.tk2msftngp13.phx.gbl...
>|||DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> Thank you again! id is unique, but I am checking if rowcount is > 1
> anyways and plan on throwing an error if it is. I plan on temporarily
> removing the unique constraint and putting in a dupe record to test it.
> Is that going too far?
> Or is it a good idea to try to handle a situation that in theory should
> never happen?
OK, now we are in for a real treat! I will show you how to do it, and if
that does not convince you that are going too far, nothing will. :-)
Andy showed you two ways, but they are a little different, which he failed
to tell. Let's look at them again:
0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
many rows -> You will get an error, "subquery returned more than one value".
0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
1 row -> @.mycol assigned the value, @.@.rowcount = 1
many rows -> @.mycol gets the last value in the result set, which that
is undefined unless you have an ORDER BY. @.@.rowcount is
set to the number of matching rows.
Look at 0 rows again:
SELECT @.mycol = 4711
SELECT @.mycol = Col FROM myTable WHERE id = 1
If there is no row with id = 1, @.mycol will remain 4711, it will not be
set to NULL.
If you really want to check for duplicates, and handle the situation
yourself, it is the SELECT assignment you want to use.
If you keep the constraints, which you should unless you have very good
reasons, the SET method is a little safer. Then again, if you know how
SELECT behaves you can be careful make sure variable is NULL before
you use it. (Yet then again, that is a trap that even season T-SQL
programmers fall into, every now and then!)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, I really should have mentioned that.
Dazed,
If you have a Unique constraint you should not have to test for
duplicates when selecting the values out. The constraint will make sure
there are no duplicates in the first place.
Andrew J. Kelly SQL MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D61468ABF3Yazorman@.127.0.0.1...
> DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> OK, now we are in for a real treat! I will show you how to do it, and if
> that does not convince you that are going too far, nothing will. :-)
> Andy showed you two ways, but they are a little different, which he failed
> to tell. Let's look at them again:
>
> 0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
> 1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
> many rows -> You will get an error, "subquery returned more than one
> value".
>
> 0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
> 1 row -> @.mycol assigned the value, @.@.rowcount = 1
> many rows -> @.mycol gets the last value in the result set, which that
> is undefined unless you have an ORDER BY. @.@.rowcount is
> set to the number of matching rows.
> Look at 0 rows again:
> SELECT @.mycol = 4711
> SELECT @.mycol = Col FROM myTable WHERE id = 1
> If there is no row with id = 1, @.mycol will remain 4711, it will not be
> set to NULL.
> If you really want to check for duplicates, and handle the situation
> yourself, it is the SELECT assignment you want to use.
> If you keep the constraints, which you should unless you have very good
> reasons, the SET method is a little safer. Then again, if you know how
> SELECT behaves you can be careful make sure variable is NULL before
> you use it. (Yet then again, that is a trap that even season T-SQL
> programmers fall into, every now and then!)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Thank you for the SET/SELECT behavior. Your reply seemed to imply (reading
in between the lines) that since the the id is UNIQUE don't bother to check
for multiple rows, SQL will error anyways in the unlikely event.
If there are duplicates in a unique column, then there is data corruption
anyways, pretty messages aren't really going to help the application faling,
it is time to contact the DBA to see why the data is corrupt.
Is that right?
I'm going nuts creating a procedure that checks for both bad and/or
duplicate data being passed into it and handling for corrupt database
information that should not happen. Seems like opening pandora's box when I
try to code pretty returns to notify the application that the database is
corrupt.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D61468ABF3Yazorman@.127.0.0.1...
> DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> OK, now we are in for a real treat! I will show you how to do it, and if
> that does not convince you that are going too far, nothing will. :-)
> Andy showed you two ways, but they are a little different, which he failed
> to tell. Let's look at them again:
>
> 0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
> 1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
> many rows -> You will get an error, "subquery returned more than one
> value".
>
> 0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
> 1 row -> @.mycol assigned the value, @.@.rowcount = 1
> many rows -> @.mycol gets the last value in the result set, which that
> is undefined unless you have an ORDER BY. @.@.rowcount is
> set to the number of matching rows.
> Look at 0 rows again:
> SELECT @.mycol = 4711
> SELECT @.mycol = Col FROM myTable WHERE id = 1
> If there is no row with id = 1, @.mycol will remain 4711, it will not be
> set to NULL.
> If you really want to check for duplicates, and handle the situation
> yourself, it is the SELECT assignment you want to use.
> If you keep the constraints, which you should unless you have very good
> reasons, the SET method is a little safer. Then again, if you know how
> SELECT behaves you can be careful make sure variable is NULL before
> you use it. (Yet then again, that is a trap that even season T-SQL
> programmers fall into, every now and then!)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>