Check and validate every value of a subquery

Question

Post reply

Check and validate every value of a subquery

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Tam Le 7 Valued Member Points: 59 More actions · Answer 1

Tam Le 7

Valued Member

Points: 59

February 14, 2011 at 8:23 am

#1286516

Thanks I'll try it out later.

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 2

Ok... I'll be the one to ask...

Why not validate and mark all the failures at once using set based SQL? Then you could actually create a report instead of possibly having a million rows of RAISERROR results.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

konstantinos.christodoulakis SSC-Addicted Points: 496 More actions · Answer 3

Jeff Moden (2/14/2011)
Ok... I'll be the one to ask...
Why not validate and mark all the failures at once using set based SQL? Then you could actually create a report instead of possibly having a million rows of RAISERROR results.

Very good idea Jeff! 🙂

Thanks!

Tam Le 7 Valued Member Points: 59 More actions · Answer 4

I don't know if Grasshopper wants to only display the subprojects that aren't null or not create the report at all if any of the subprojects are null. If it's the latter, Grasshopper can continue checking the rest of the subprojects based on the previous subproject's @RetVal value. If the @RetVal = 'NotNull' then check the next subproject. If @RetVal = 'NULL' then there's no need to check the rest of the subprojects.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

konstantinos.christodoulakis (2/15/2011)
Jeff Moden (2/14/2011)
Ok... I'll be the one to ask...
Why not validate and mark all the failures at once using set based SQL? Then you could actually create a report instead of possibly having a million rows of RAISERROR results.
Very good idea Jeff! 🙂
Thanks!

Isn't that exactly what you rejected earlier in this thread?

konstantinos.christodoulakis (2/13/2011)
mister.magoo (2/13/2011)
There is... but why are you opposed to just returning the dataset containing the problem subprojects?
The case is the following:
On a .NET app I have a button with which a SP is executing and creates an XML Exported file.
So I created a SP with a very big query to export the XML using FOR XML PATH.
During execution of SP, I need to check every field (there are almost 100 fields) and verify that there are NO NULL values.
So the case I described with SUBPROJECT START DATE is only one of my cases.
I have a lot of other cases with checks on another tables with other subqueries.
So, I believe that to post here the whole DDL will be more confusing than helpful.
Do you have any other idea to help me out?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 6

mister.magoo (2/15/2011)
konstantinos.christodoulakis (2/15/2011)
Jeff Moden (2/14/2011)
Ok... I'll be the one to ask...
Why not validate and mark all the failures at once using set based SQL? Then you could actually create a report instead of possibly having a million rows of RAISERROR results.
Very good idea Jeff! 🙂
Thanks!
Isn't that exactly what you rejected earlier in this thread?
konstantinos.christodoulakis (2/13/2011)
mister.magoo (2/13/2011)
There is... but why are you opposed to just returning the dataset containing the problem subprojects?
The case is the following:
On a .NET app I have a button with which a SP is executing and creates an XML Exported file.
So I created a SP with a very big query to export the XML using FOR XML PATH.
During execution of SP, I need to check every field (there are almost 100 fields) and verify that there are NO NULL values.
So the case I described with SUBPROJECT START DATE is only one of my cases.
I have a lot of other cases with checks on another tables with other subqueries.
So, I believe that to post here the whole DDL will be more confusing than helpful.
Do you have any other idea to help me out?

Heh... it's all "in the wrist". 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

baabhu SSCertifiable Points: 6205 More actions · Answer 7

Hi,

Can you try this SQL.

if Exists (select COUNT(*) from PROJECT Inner join SUBPROJECT ON PROJECT.ID = SUBPROJECT.ID

Where (PROJECT.ID=@IDProject)

and Subproject.StartDate is null) > 1

BEGIN

RAISERROR ('Subproject Start Date cannot be NULL.',11, 2) WITH NOWAIT

END

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

Jeff Moden (2/15/2011)
Heh... it's all "in the wrist". 😛

I wouldn't mind if the rejection of that idea hadn't meant I spent time looking for other ways...:crazy:

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);