• Dynamic SQL and Stored Procedures

Fundamentally, use Dynamic SQL (that is, SQL built "on the fly" in code) when it is impossible to use Stored Procedures. Many SQL servers cannot use late binding on database objects. In such servers, stored procedures cannot represent table names or column names as variables. When the tables and columns change on the fly, Dynamic SQL is the way to display or affect data.

Beyond this fundamental, the use of stored proc's or Dyamic SQL is matter of code management style. Do you want to deal with hundreds of mysterious stored proc's on the server? Do you want to "hard code" or store SQL strings in "meta-data" tables or client-side application files?

• Views and SELECT Stored Procedures

In short, a View is just another table compared to a Stored Procedure. To show a View, a SQL SELECT statement must be used which is compiled on demand. This is very much unlike a Stored Procedure, which is pre-compiled and "planned" (if, say, WITH RECOMPILE is used for Sybase SQL Servers).

SELECT Stored Procedures are smaller and faster than Views. They are also more complex than Views. They can run loops, decision structures and other logic a View was not designed to handle.

The following is an example of a SELECT Stored Procedure for a Sybase SQL Server:

IF OBJECT_ID('rpt_get_field_names') IS NOT NULL
    BEGIN
    DROP PROCEDURE rpt_get_field_names
IF OBJECT_ID('rpt_get_field_names') IS NOT NULL
    PRINT '<<< FAILED DROPPING PROCEDURE rpt_get_field_names >>>'
ELSE
    PRINT '<<< DROPPED PROCEDURE rpt_get_field_names >>>'
END
GO

CREATE PROCEDURE rpt_get_field_names

WITH RECOMPILE

AS

SELECT
  t_field.field_id
, t_field.name
, t_field_lookup.value

FROM 
  t_field
, t_field_lookup

WHERE t_field.field_id = t_field_lookup.field_id

ORDER BY
  t_field.name
, t_field_lookup.value
GO

GRANT EXECUTE ON rpt_get_field_names TO public
GO
IF OBJECT_ID('rpt_get_field_names') IS NOT NULL
PRINT '<<< CREATED PROCEDURE rpt_get_field_names >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE rpt_get_field_names >>>'
GO