Stored Procedure Return Value Filtering 2

[BoulderBum]

I've set up a stored procedure like:

IF (some condition is true)
  BEGIN
    SELECT bunchOfStuff
    FROM aTable
  END
ELSE
  BEGIN
     SELECT bunchOfStuff
     FROM aTable
     WHERE blahBlah
  END

It works fine, but for maintainability reasons, I'd like to take a different approach and am wondering how I would do so (I don't really understand how return values work in the procedure. Is it just what is SELECTed at the end?)

Basically, I'd like to restructure the above to say:

( SELECT bunchOfStuff
FROM aTable ) as results

IF( some condition is true )
  BEGIN
     SELECT * 
     FROM results
     WHERE blahBlah
  END

Where either "results" would get returned or a filtered version of "results" would get returned. How would I go about doing what I want to do?

 

[SQLSister]

Your original thought is the more efficient way to go. The second way requires you to select the whole table once and then the filtered results which will use more network and system resoucres than only running the appropriate select statement. Of course, what you wrote inthe second suggestion will not work from a syntax point of view but that could be fixed if it was a viable way tog o. I do not see why the second approach would be more maintainable tahn the first approach. Your original approach is ok.

As to what is returned from a stored procedure. From Books online:
"The RETURN statement unconditionally terminates a query, stored procedure, or batch. None of the statements in a stored procedure or batch following the RETURN statement are executed.

When used in a stored procedure, the RETURN statement can specify an integer value to return to the calling application, batch, or procedure. If no value is specified on RETURN, a stored procedure returns the value 0."


Any select statements through a stored procedure that are not used to assign vaiables or as part of an insert, update or delete will be returned by the stored procedure whether you use the return keyword or not. This can sometimes present problems if you have more thanone select statemetn that will run as both record sets will be returned and the application has to be told which one to use where.

 

[BoulderBum]

"I do not see why the second approach would be more maintainable tahn the first approach."

It's a hugeo query, and if the criterion for the search change, I would have to remember to update EACH select statement (and hope I update each one right). With the alternate approach only one change is made to update both queries, reducing the chance for error and enhancing readability.

"Any select statements through a stored procedure that are not used to assign vaiables or as part of an insert, update or delete will be returned by the stored procedure whether you use the return keyword or not."

So if I WERE to choose the second approach (which I may not) I would want to store the results of the first query to a variable, the either return that variable, or the filter result?

 

[SQLSister]

Yes return the results of a select to a table variable or temp table if you do not want the user to see them.

If you have a complex stored procedure, it is even more important to consider efficiency of running it. Returning a whole record set you do not need will slow your system down, possibly tremendously, depending on the number of simultaneous users. System efficiency is almost always more important than maintainability.

Have you considered using a case statement in your where clause to account for the differnt possibilities?

 

[SQLBill]

Maintaining code is always 'fun'. The one thing you can do to make it easier is to use COMMENTS.

For example, start all your code with:

/*
Program Name: usp_myproc.sql
Programmer: BoulderBum
Date Created: October 21, 2003
Date Updated: October 21, 2003
Purpose: To display a bunch of stuff from tablea
based on certain criteria.
Notes: If the criteria needs to be changed, there are
two sections that need to be checked.
*/
--check this IF when changes are being made to criteria
IF (some condition is true)
BEGIN
SELECT bunchOfStuff
FROM aTable
END
ELSE
BEGIN
--check this WHERE when changes are being
--made to the criteria
SELECT bunchOfStuff
FROM aTable
WHERE blahBlah
END

-SQLBill

 

[BoulderBum]

Both good suggestions. Thanks, guys.

Comments fail to achieve the elegance and ease of maintainence of the alternate approach in my view (I'm an OOP guy, I prefer modular approaches), but it's nice to be aware of the performance trade-off and documentation suggestion (I was wondering about the comment syntax in a SQL environmnet!).

 

[SQLBill]

Comments in SQL Server are identified two ways:

Block comments are started with /* and ended with */ in between you can use ** at the beginning of each line, but it's not required:

/*this is a block comment*/
/*this is another example
of a block comment */
/*this is a third example
** of a block comment*/

Block comments cannot be in the middle of code.

Single line comments can be anywhere in code and are identified by -- at the beginning of the comment.

SELECT *
--this is an example of single line comments
--in code
FROM mytable

SELECT * --this is another example
FROM mytable

-SQLBill

 

[SQLSister]

The first rule of database performance is NEVER return any more than you absolutely have to. Suppose you have a table with a million records. In your scenario, you first return all million records and then send another query to the database to return the five records you actually wanted. The difference in processing between the two approaches could range from minutes to millseconds.That is why this is such an inefficient way to do business.

Since many of us don't develop with a test database that has anywhere near the number of records that the final production database will have, often these performance problems are unnoticed until a year or more after production and then are very hard to fix because to do so will cause major restructuring of both the database calls and the user interface code.

It is often difficult to track down just what caused the system to run at glacial speed, too, when you have thousands of users running thousands of stored procedures against hundreds of tables. This is why it is critical to take performance into account as you design stored procedures.

Very small gains in the performance of one stored procedure can improve performance dramatically when they are multiplied by the number of simultaneous users.

When you consider that the programmers who used an inefficent approach probably programmed the rest of the system, you can find yourself modifying many or most of the stored procedures to gain the performance you need. And this will be done in a high stress environment because the production system is critical and the users are unhappy. Not the most fun situation for making database programming modifications. So you see, the less elegant approach actually turns out to be the one easier to maintain if it provides the performance you need.

Always remember, users don't give a darn about elegance of coding; they care immensely about performance.

 

[BoulderBum]

"The first rule of database performance is NEVER return any more than you absolutely have to."

Actually, my idea was to store the results in a table (via a procedure variable), optionally filter the results of the table, then return whatever was left (as opposed to returning multiple tables).

Nevertheless, point taken. Thanks again.

 

[BoulderBum]

SQLBill,

When you say block comments can't appear in the middle of code, do you mean that they must appear at the head of a document only, or just not in the middle of a SELECT statement or something?

In other words, is the following legal:

SET --blah blah

/*block comment
**between
**some stuff*/

SELECT --blah blah

 

[SQLSister]

Storing the results in a table still returns them just not to the user. The database still has to process all the records and store them in the table variable or temp table.

Perhaps returns was a poor word choice on my part, but I really can't think of a substitute term right now. Perhaps if I had gotten some sleep last night, I could be more articulate.