Stored Procedures - general question 1

[vpekulas]

I'm just starting with stored procedures, I seem to understand the syntax quite well (at least now),
but what I never found out is if it's a good idea to completely replace dynamic SQL statements
by stored procedures.

Let's say that I have a simple application (ASP) with say 25 SQL queries, each performing something
little bit different. Should I have 25 stored procedures then ?

Thanks in advance.

"Taxes are the fees we pay for civilized society"

 

[jhall156]

In SQL Server you can use parameterized stored procedures. It's okay to use EXEC(sqlstr) but you won't get the benefits of a saved execution plan.

JHall

 

[rac2]

Sometimes queries that are a "little bit different" can be combined into a single query. Or a set of queries can be placed into a single stored procedure with logic to decide which query is needed. These techniques will give the advantage of saving the execution plan.

Obviously queries that differ only in the SELECT list can be combined.

Queries that differ in the WHERE clause by including or omitting various conditions can be combined with the use of an indicator variable. Like so-

SELECT ...
FROM ...
WHERE
      ( @anyStartTime = 1 OR start_time = @startTime )
  AND ( @anyPlace = 1 OR place = @place )

If the start time is not a criterion then set @anyStartTime = 1; if place is not a criterion then set @anyPlace = 1. Thus you have one query that expresses three different searches, one based on start time, one based on place, and one based on both start time and place.

Queries that serve similar functions but are completely different can also be placed in the same stored procedure. This can simplify the coding of your ASP application.

For example, a report from a call logging system that was revamped. The tables in the old system have historical data in a different table structure than the new system. The report can be run for a time period that may or may not bridge the old and new systems. The ASP code calls a single stored procedure with the date range. The logic in the stored procedure determines whether to query the old tables, the new tables, or, heaven forbid, query both tables and combine the results in a temporary table.

So the application logic does not even deal with the two different systems; and the various queries, which are moderately complex, have been compiled and are ready to run whatever date range is requested.

 

[vpekulas]

Thanks guys, much appreciated.

"Taxes are the fees we pay for civilized society"