sp_depends return value
-
Hi all, I have an admin sproc that I've used for a long time; but apparently I never noticed that sp_depends doesn't return a failure value. I suppose over time I have just never checked a sproc that had no dependencies which is how I discovered this. sp_depends raises an error if there are no dependencies unfortunately the return Value is 0 for all cases. Therefore using checking the return value is of no use. Since I use a return Value and a retCode in my sproc I was wondering if there is some system variable I can use to get the last error raised when exec a sproc from a sproc?? Currently I am checking for the table count and if it is 0 then I display the same message sp_depends would. I would rather the error number be returned in my return code so I can look up the mesage or have a custom message of my own. Thanks Pamela Reinskou VersusLaw Inc.
-
Hi all, I have an admin sproc that I've used for a long time; but apparently I never noticed that sp_depends doesn't return a failure value. I suppose over time I have just never checked a sproc that had no dependencies which is how I discovered this. sp_depends raises an error if there are no dependencies unfortunately the return Value is 0 for all cases. Therefore using checking the return value is of no use. Since I use a return Value and a retCode in my sproc I was wondering if there is some system variable I can use to get the last error raised when exec a sproc from a sproc?? Currently I am checking for the table count and if it is 0 then I display the same message sp_depends would. I would rather the error number be returned in my return code so I can look up the mesage or have a custom message of my own. Thanks Pamela Reinskou VersusLaw Inc.
The
@@ERRORvariable holds the last error number. -
The
@@ERRORvariable holds the last error number.Yes, this is true; but in this case it is overwritten as soon as the next statement in the sp_depends sproc is executed. Therefore by the time my sproc receives the output the @@Error is 0 again. I was thinking maybe there was some undocumented system function(s) that handles scope much like the identity functions, Scope_Identity and @@Ident_current. You can check this for yourself by comparing the results of sp_depends on an object that has dependencies and one that doesn't, set a local var to return the returnValue and also try to grab the @@Error and you will see in all cases the returnValue is 0 and @@Error is 0. For now I am trapping it and using table count that seems to be the only way to figure it out. Thanks. Pamela Reinskou VersusLaw Inc.