custom result after reviewing a stored procedures' result

Question

I want to return custom values as the values of the rows in case when no rows are fetched by executing the stored procedure

How do i do that?

Answer 1

Just do your own Select statement containing the values you want to return to the user. Something like this:

Select
   MyValue1,
   MyValue2,
   Myvalue3

Answer 2

You can use the built-in function ROW_COUNT() to test if any rows have been returned from a query. If the number of rows returned is zero, then you can run an alternative SQL statement to return your custom values:

--Execute your SQL statement
SELECT * FROM myTable

IF ROW_COUNT() = 0 
BEGIN
     --SQL to return your custom values instead
     SELECT value1, value2, etc.
END