>>>Hi,
>>>
>>>In my VFP stored procedures I always returned a string; empty if no problem occurred; or the string would have a description of the error, if a problem occurred.
>>>
>>>In SQL Server stored procedures, I learned today, I can only return an integer. I am thinking about returning 0 if no problem and -1 if there is a problem. Then, if there is a problem, set to an OUTPUT parameter the value of error message. I am wondering if this would be a good practice? My concern is that then each and every stored procedure would have to have an OUTPUT parameter.
>>>
>>>I would appreciate any suggestions on this topic. TIA.
>>
>>I think the best practice is to send the error back as an error and deal with that exception on the client side. You'll get all the info. Alternatively, catch that error in SQL Server and pass back to the client with some extra error info you may find desirable. See samples in my SP:
>>
How to insert information into multiple related tables and return ID using SQLDataSource>>
>>It shows how to trap the error and pass the error back to the client.
>
>Do I understand correctly that the OUTPUT parameter (as a way to return an error description) is not a good approach (in your opinion)? But instead call RAISERROR ('Error description ...')?
Yes. I don't think it's a good idea in general to return an error as OUTPUT parameter, although there may be cases when this is the desired approach. For most cases I'd say we need to handle errors in the calling application.
If it's not broken, fix it until it is.
My Blog