Returns 0 rows but which part of the where clause caused it.

I currently have a piece of SQL that will return a row if the input is
valid for the process. My application is a warehouse system. I need to
verify that the thing that is picked for the order can really be used
for that order. It is the correct product, it is in the correct status,
in the correct warehouse, ... Well up until now used one SQL statement
to achieve this. The row was returned or not. If not then the thing
being picked could not be used. Now my boss wants me to return why.
Essentially i will have to get the data and process this in code. Does
anyone have a SQL solution to this? Any tricks anyone uses to determine
which part(s) of the where clause (or join) caused the exclusion?
danWhat if more than 1 condition is FALSE?
There's lots of ways you could implement this, but sooner or later you'll
have to test each condition individually to find the cause.
You could test them up front and not run the query if an "error" condition
is found, or you could run the query and only test the conditions if
@.@.ROWCOUNT = 0.
You could indicate the reason by using RAISERROR, or maintain a table of
error codes and return them in the query, or you could ...
"Dan Holmes" wrote:

> I currently have a piece of SQL that will return a row if the input is
> valid for the process. My application is a warehouse system. I need to
> verify that the thing that is picked for the order can really be used
> for that order. It is the correct product, it is in the correct status,
> in the correct warehouse, ... Well up until now used one SQL statement
> to achieve this. The row was returned or not. If not then the thing
> being picked could not be used. Now my boss wants me to return why.
> Essentially i will have to get the data and process this in code. Does
> anyone have a SQL solution to this? Any tricks anyone uses to determine
> which part(s) of the where clause (or join) caused the exclusion?
> dan
>

returning the table that caused a contraint error

HI all,
I have, as per normal, relations setup with delete contraints on the parent
child relation.
Now I have a table that has many such child relations and constraint. As a
result when a user tries to delete the parent, a correct message is
triggered.
My problem is, trying to determine which table is the cause of this
contraint error. I can see the table mentioned in the 547 error that SQL
produces. I was wondering if there is a SP or something that could return
just the table name, that I could use, instead of wading through the error
message.
Thanks
Robert
hi Robert,
Robert Bravery wrote:
> HI all,
> I have, as per normal, relations setup with delete contraints on the
> parent child relation.
> Now I have a table that has many such child relations and constraint.
> As a result when a user tries to delete the parent, a correct message
> is triggered.
> My problem is, trying to determine which table is the cause of this
> contraint error. I can see the table mentioned in the 547 error that
> SQL produces. I was wondering if there is a SP or something that
> could return just the table name, that I could use, instead of wading
> through the error message.
this should be the scenario you mean...
SET NOCOUNT ON
USE tempdb ;
GO
CREATE TABLE dbo.MasterT (
ID int NOT NULL PRIMARY KEY ,
Data varchar(10) NOT NULL
) ;
CREATE TABLE dbo.DetailT (
ID int NOT NULL PRIMARY KEY ,
IdMasterT int NOT NULL
CONSTRAINT FK_MasterT_DetailT
FOREIGN KEY REFERENCES dbo.MasterT (ID),
Data varchar(10) NOT NULL
) ;
GO
INSERT INTO dbo.MasterT VALUES ( 1 , 'value' ) ;
INSERT INTO dbo.MasterT VALUES ( 2 , 'value' ) ;
INSERT INTO dbo.DetailT VALUES ( 1 , 1, 'value' ) ;
INSERT INTO dbo.DetailT VALUES ( 2 , 1, 'value' ) ;
INSERT INTO dbo.DetailT VALUES ( 3 , 2, 'value' ) ;
GO
DELETE dbo.MasterT
WHERE ID = 1 ;
GO
DROP TABLE dbo.DetailT , dbo.MasterT ;
all you can get is the error you already noted, 547, which reports the
actual problem indicating the constraint name, the involved table and colum,
Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_MasterT_DetailT'. The conflict occurred in database 'tempdb', table
'DetailT', column 'IdMasterT'.
The statement has been terminated.
no other way is allowed/possible...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply