February 13, 2011 at 3:17 pm
Dear all,
I have a case in which I want to validate some values with a Stored Procedure and return a custom RAISERROR, but I'm not sure how to implement it.
THE CASE:
I have a Table named PROJECT.
Each PROJECT has more than one SUBPROJECTS.
I want to validate every SUBPROJECT and IF SUBPROJECT'S START DATE is NULL then to RAISEERROR and a message for which is the SUBPROJECT with NULL START DATE.
My query:
IF
(SELECT SUBPROJECT.STARTDATE
FROM PROJECT
INNER JOIN SUBPROJECT ON PROJECT.ID = SUBPROJECT.ID
WHERE (PROJECT.ID=@IDProject)) IS NULL
BEGIN
RAISERROR ('Subproject Start Date cannot be NULL.',11, 2) WITH NOWAIT
END
It returns an ERROR as the subquery returned more that one value, so it cannot validate more than one values!
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
HOW can I check each of the subquery values?
With CURSOR? Which is it's syntax in my case?
Any other idea?
Regards,
Konstantinos
February 13, 2011 at 3:23 pm
Do you want to validate every subproject for the @ProjectID?
Or are you just interested if there EXISTS (hint) at least one subproject with a null start date?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 13, 2011 at 3:26 pm
I want to check IF EXIST a NULL SUBPROJECT START DATE (checking all SUBPROJECTS) and THEN to RAISERROR.
February 13, 2011 at 3:35 pm
Let me re-phrase the question.
If one project has ten subprojects and two subprojects have a null start date do you:
(pick one please)
1) Raise one error
2) Raise one error per subproject with a null start date
Also, are you sure RAISERROR is the correct way to report this? How are you picking that information up and informing the end user?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 13, 2011 at 3:40 pm
Yes, i want to:
2) Raise one error per subproject with a null start date
I'm catching every RAISERROR from .NET application
(catch (SqlException ex)
February 13, 2011 at 3:48 pm
I would recommend reading CELKO's post about table design...
Having said that, if you are not able to change database design at this time (let's face it - who can once it's in production?), then you may want to consider changing the way this operates.
The problem is that you cannot raise a SET of errors.
If you just return the set to the .NET application it is simple to then inform the user from that dataset
SELECT SUBPROJECT.ID,<any other columns you like>
FROM SUBPROJECT
WHERE SUBPROJECT.ID = @ProjectID
AND STARTDATE IS NULL
However, I just noticed that the join you showed was SUBPROJECT.ID = PROJECT.ID - are you sure that is right - the ID column in SUBPROJECT relates to the ID column in PROJECT? I take it you also have a SUBPROJECT.SUBPROJECTID or similar?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 13, 2011 at 4:00 pm
Thank you for your answers.
My initial question is:
How can I check every value which returned from my subquery and return a RAISERROR for every check from inside the SP.
You tell me that is impossible to have more than one RAISERRORS on the same query?
Is the cursor a solution for that?
February 13, 2011 at 4:10 pm
There is... but why are you opposed to just returning the dataset containing the problem subprojects?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 13, 2011 at 4:21 pm
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?
February 13, 2011 at 4:41 pm
I may have an idea but I need to check on my syntax - it's late ! back in a bit.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 13, 2011 at 5:17 pm
Hmmm it is a bit of a problem you have there, and the only thing left in my kit bag tonight is this - feel free to ignore it as I am not sure it is very workable.....
Firstly, it involves allowing NULLS into the XML by adding XSINIL to the XML PATH clause
e.g.
select @xml=(select 1 as a,2 as b,3 as c,null as d
for xml path('t'),ELEMENTS XSINIL,type)
This produces output such as this
<t xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<a>1</a>
<b>2</b>
<c>3</c>
<d xsi:nil="true" />
</t>
Now, you can go ahead and generate your XML, then test it for nulls like this:
;with xmlnamespaces( 'http://www.w3.org/2001/XMLSchema-instance' as xsi)
select
x.n.query('fn:local-name(.)') as NullNodeName,
x.n.query('fn:local-name(..)') as ParentName,
convert(varchar(MAX),x.n.query('..')) as ParentNode
from @xml.nodes('//*[@xsi:nil="true"]') x(n)
This (or variants of this technique) would provide you with a row set that contained details (which details you chose to include would depend on your specific xml) of any NULLS in the source data...
As I said, it is not neccessarily a good option, but the only other one I can see is to specifically code NULL checks into every select in your SP using cursors to throw errors, which is imho probably worse than this approach...
Good luck figuring this out and please do post back your final solution - it is certainly an interesting problem!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 14, 2011 at 12:11 am
Hi if your getting the following error your subquery contains more than one record:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Write a loop for each subquery to check each field in each record. If any of the fields contain a null value, exit the loop and return a null value.
February 14, 2011 at 1:31 am
Tam Le 7 (2/14/2011)
Hi if your getting the following error your subquery contains more than one record:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Write a loop for each subquery to check each field in each record. If any of the fields contain a null value, exit the loop and return a null value.
Hello,
Yes, this is the objective and my question too.
Can this be done with a cursor? How?
This is my question too.
February 14, 2011 at 8:09 am
Cursors are SLOW! Use a loop instead. Try something like this:
DECLARE @Count INT,
@LoopCount INT,
@MyField1 VARCHAR(50),
@MyField2 VARCHAR(50)
SELECT IDENTITY(INT,1,1) ID, MyField1, MyField2 INTO #Temp FROM MyTable
SELECT @Count = @@RowCount
SET @LoopCount = 1
WHILE @LoopCount <= @Count
BEGIN
SET @MyField1 = (SELECT MyField1 FROM #Temp WHERE ID = @LoopCount)
SET @MyField2 = (SELECT MyField2 FROM #Temp WHERE ID = @LoopCount)
IF @MyField1 IS NULL OR @MyField2 IS NULL
BREAK
Set @LoopCount=@LoopCount + 1
END
IF @MyField1 IS NULL OR @MyField2 IS NULL
SET @RetVal = 'NULL'
ELSE
SET @RetVal = 'NotNull'
DROP TABLE #Temp
February 14, 2011 at 8:14 am
Tam Le 7 (2/14/2011)
Cursors are SLOW! Use a loop instead.
True cursors are slow but this looping is no better.
http://bradsruminations.blogspot.com/2010/05/truth-about-cursors-part-1.html
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply