error handling in SQL stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

error handling in SQL stored procedure

Author

Topic

ITSSQL
Starting Member

8 Posts

Posted - 2008-03-12 : 14:47:15

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-12 : 15:13:48

Do you want just ignore the error and keep the bad code or do you want to fix the code that is causing the error to happen?

ITSSQL
Starting Member

8 Posts

Posted - 2008-03-12 : 15:23:49

thanks for looking into it. For a moment, I would like to ignore this error message. But if there will be any other error message then I would like to take care of it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources