Stored Procedures

[Rocks]

Hi all,


Just a question on Stored Procedures. I am new to VB and to SQL, I have to add some error handling to some stored procedures. They are all Select procedures that are passed varibles from VB. They do not return anything.

I just want to learn how report any errors (if any) to the vb app and display them to the user.

Is there any one out there that can help????


Thanks in advance
Rocks,

[cajsoft]

Can you expand on this..? what error handeling are you coding into your stored procedure?

[Rocks]

Hi,

What I would like to do is to check if the statement has been processed, and where statements use Varibles, I would like to check if they have been passed, are not null.

If you like I can mail you a stored procedure that I am working on.....


Regards,

Rocks

[cajsoft]

I use a stored procedure, and check the results are not null by using this statement below, using a UserConnection

Dim rd as rdoresult
If IsNull(rd!fieldname)Then
       blah de blah
End if

The ISNull check the returned data is not NULL.

Craig.

[Lafor]

In the stored proc
(after the select)

IF @@ERROR <> 0
goto MyError

return (0) -- Success


MyError:
return(1) -- Failure

or any error value you want


Now in your VB

you can check for these values


That's just one way...

[Rocks]

lafor.

I managed to get that far what I would like to lnow is how to Pull the error info out of the store proc into VB...


any advice???


Cheers mate

[Lafor]

When an error occurs the global var

Err is automatically populated with the necessary info
including err number and description

if in your VB, you have

OnError goto CHrisjk (at the top of your proc)


then

Chrisjk:
MsgBox "Sorry! Error '" & Err.Description '& " 'While Executing this stored proc name here "