Error Handling In SQL Stored Procedure

Hi all,

I have a huge stored procedure. part of the query is, the cursor is open and it consist of the implementation for subquery, I want to do an implementation if this subquery return more than one value, catch the error, but continue the cursor operation.

my SQL procedure part looks like:

---code to open the cursor here
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @attachmentCount=count(metaDataID)
FROM View_1
WHERE parentMetaDataID='' + ( SELECT metaDataID
FROM View_1
WHERE metaDataStorageID = @metaStorageID AND parentMetaDataID='0') + '' AND
metaDataContentTypeID=@metaDataContentTypeID

--error handling
DECLARE @err int
SELECT @err = @@error
IF @err <> 0
BEGIN
FETCH NEXT FROM CursorDataStorageID INTO @metaStorageID
CONTINUE
END

FETCH NEXT FROM CursorDataStorageID INTO @metaStorageID
END

-----------------

when I execute this query in SQL management studio, it does return me the no of rows with the msg:

Msg 512, Level 16, State 1, Procedure sp_AdvanceSearchHugeExecution, Line 522

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


But when the stored procedure is called from the within the code ASP.Net, to fill the dataset, I get an exception.

my C# code is simple to fill the dataset using data adapter in try catch block.

Can anyone please suggest how will I able to fill dataset successfully overcoming this mesg?

Thanks a lot.