May 18, 2020 at 2:23 pm
A while ago, I was testing stored procedure return codes. I'm preparing for the 98-364 Database Fundamentals exam and I just took another look at my notes on stored procedures. Here is some basic code I wrote just to get an idea of how to work with these return codes. At the time I wrote this, I understood it, but now I have some questions after taking another look.
CREATE PROCEDURE dbo.dogTest
AS
BEGIN
SELECT 'Dog'
END
-------------------------------------------
DECLARE @return_value int -- declaring an variable of type int
EXECUTE @return_value = dogTest; -- stored proc seems to have no problem executing, but shouldn't this assignment fail since the SP returns
-- text and @return_value can only hold an int?
SELECT 'Value Returned' = @return_value
The stored procedure returns the word Dog. So far, so good.
The SELECT statement returns a zero. This is where I am a bit confused.
If the procedure runs successfully, a return code of zero is generated. On the line containing the execute statement, we have a call to the procedure dogTest. That procedure appears to run successfully. However, the procdure returns a text string which is being assigned to a variable called @return_variable which is defined to hold an int value. Shouldn't this assignment fail, because SQL can't convert a text string to an int?
Also, when you retrieve a stored procedure's return code, although it is apparently possible to declare any variable like I did (i.e. @return_variable), is this really the right way to do it, or is there a predefined system variable that you should reference instead?
One more question. There is the creation of a stored procedure. Then there is the execution of a stored procedure that has been successfully created. When a return code other than zero is returned (meaning the procedure didn't run properly), does this occur when the procedure is created or when the procedure is called/executed?
May 18, 2020 at 2:33 pm
You have to use RETURN somevalue or variable in the proc to get such a return.
See the following...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2020 at 2:55 pm
I have never understood the utility of returning the bit value from a stored procedure execution. It's too abstract of a concept to say "it worked" or "it didn't work" unless you're comparing values against a known result set. "It executed without errors" is able to be evaluated by checking for errors, so I continue to be annoyed at this particular functionality.
Jeff probably has a good use case for it that I've never run across. He's old as dirt, and therefore knows where all the bones are buried.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
May 18, 2020 at 3:04 pm
Jeff,
Thanks for the link. Now I understand how to obtain the return code.
Here is the only part I don't understand. Using the code from my example, how might the procedure return a code other than zero?
If I put a "\" at the end of the SELECT line in the procedure to induce an error, I get an error because I can't create the procedure. I guess it wouldn't even be possible to generate a failed return code here because the procedure wasn't even created successfully.
After running the correct code in my original example to create a procedure, I also tried to induce an error when calling that procedure by just adding an '&' at the very end of the execute statement, i.e. after the procedure name. This caused an error but @return_value doesn't return a nonzero because the assignment didn't work in the first place.
I'm trying to think of a simple situation where the procedure would return a nonzero to @return_value.
May 18, 2020 at 3:20 pm
I have never understood the utility of returning the bit value from a stored procedure execution. It's too abstract of a concept to say "it worked" or "it didn't work" unless you're comparing values against a known result set. "It executed without errors" is able to be evaluated by checking for errors, so I continue to be annoyed at this particular functionality.
Jeff probably has a good use case for it that I've never run across. He's old as dirt, and therefore knows where all the bones are buried.
I have never understood the utility of returning the bit value from a stored procedure execution. It's too abstract of a concept to say "it worked" or "it didn't work" unless you're comparing values against a known result set. "It executed without errors" is able to be evaluated by checking for errors, so I continue to be annoyed at this particular functionality.
Jeff probably has a good use case for it that I've never run across. He's old as dirt, and therefore knows where all the bones are buried.
I agree the "it worked" or "it didn't work" is pointless.
However I use it to return a status for "soft" errors (data issues, duplicate updates etc) so that my application can decide different logic paths or a terminal error. Yes, you could return it in a result set but it seems overkill to me for a simple return.
But as our resident guru always says "it depends"
Far away is close at hand in the images of elsewhere.
Anon.
May 18, 2020 at 3:31 pm
Jeff,
Thanks for the link. Now I understand how to obtain the return code.
Here is the only part I don't understand. Using the code from my example, how might the procedure return a code other than zero?
If you read the entire link, then you saw examples like the following...
B. Returning status codes
The following example checks the state for the ID of a specified contact. If the state is Washington (WA), a status of 1 is returned. Otherwise, 2 is returned for any other condition (a value other than WA for StateProvince or ContactID that did not match a row).
USE AdventureWorks2012;
GO
CREATE PROCEDURE checkstate @param varchar(11)
AS
IF (SELECT StateProvince FROM Person.vAdditionalContactInfo WHERE ContactID = @param) = 'WA'
RETURN 1
ELSE
RETURN 2;
GO
The reason for different numbers can be to let the caller (proc or human or script) make a processing decision as to what to do next based on the exit code (the RETURN code). For example, in an ETL proc, you might want to return a "1" if a file was found and successfully processed, a "2" if the file was found and had no rows in it, a "3" if a file was found but had errors in it", or a "4" if a file wasn't found.
For another proc, it might simply contain the number of rows that were processed. In others, it could be used as a simple error indication where "0" means no errors and any non-zero means there was an error. You could actually return @@ERROR as part of a TRY/CATCH.
It, of course, can be used as an "early out" of the proc and having those "early outs" numbered can be of help as I laid out above.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2020 at 3:35 pm
Jeff,
Thanks for the link. Now I understand how to obtain the return code.
Here is the only part I don't understand. Using the code from my example, how might the procedure return a code other than zero?
If I put a "\" at the end of the SELECT line in the procedure to induce an error, I get an error because I can't create the procedure. I guess it wouldn't even be possible to generate a failed return code here because the procedure wasn't even created successfully.
After running the correct code in my original example to create a procedure, I also tried to induce an error when calling that procedure by just adding an '&' at the very end of the execute statement, i.e. after the procedure name. This caused an error but @return_value doesn't return a nonzero because the assignment didn't work in the first place.
I'm trying to think of a simple situation where the procedure would return a nonzero to @return_value.
To be sure, it's not useful for compile-time errors and will only be useful for run-time errors if you're actually trapping for errors. See my comment above for other uses, which I've used quite a bit in the past.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2020 at 3:37 pm
And, no... not as old as dirt. I'm older than a lot of the dirt I made in my garden. 😀 I've got sox older than some of your adult children... and, yeah... I really need to get some new ones. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2020 at 10:21 pm
Jeff,
Yes, I saw that part about returning various numbers. I think I might have misunderstood what is meant by the procedure generating an error. I took that to mean if the procedure itself didn't execute successfully. However, now it looks like it's a question of whether or not the code inside the procedure (not the entire procedure itself) would generate an error.
So I think I've got it now. I see what you mean about returning various numbers depending on the outcome of the code in the stored procedure.
May 19, 2020 at 1:31 am
Excellent. Thanks for the feedback, Michael.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 19, 2020 at 10:43 pm
Jeff,
I think I'm pretty much ready to test for the Microsoft 98-364, Database Fundamentals exam.
Just to be really sure I will pass it, I'm studying for the Microsoft 70-761, Querying Data with Transact SQL exam. I figured if I aim that high, then surely passing the 98-364 should be fairly easy.
Then after passing the 98-364 exam, I will try the 70-761 exam.
This message forum has helped quite a bit. I have received many good tips and suggestions which I believe will really help me.
May 20, 2020 at 4:56 am
Good luck on the exams, Michael. When you pass them, maybe you can teach me about XML! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2020 at 5:12 pm
Good luck on the exams, Michael. When you pass them, maybe you can teach me about XML! 😀
I don't know about that. I don't think XML will be on the DB fundamentals exam. If so, probably just very general questions. Not sure about the exam about writing queries. To study more for that, after I get through the latest stuff I discovered I should study (EXISTS, PARSE, CONVERT, etc.), I am thinking about buying some practice exams for that.
I really appreciate all of your suggestions. Very helpful.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply