Posted by Erland Sommarskog on 04/11/06 13:52

Water Cooler v2 (wtr_clr@yahoo.com) writes:
> 1) What are SQL functions and how are they different from stored
> procedures? Do both of the programming objects not achieve the same
> thing? What was the need of having one in addition to the other?

A function is a much restricted module. A function may not change database
state, and therefore you cannot perform INSERT, UPDATE or DELETE statements
except on table variables (which are local to the function). Nor can you
call stored procedures, nor use dynamic SQL.

There are two main types of functions: scalar and table-valued. The latter
in their falls into inline and multi-statment. Inline functions consists
of a single query and are really parameterised views.

Functions comes in handy at times, but stored procedure remains the main
programming object. Scalar functions that accesses data is something to
be restrictive with, since if they appear in a SELECT, they can cause
serious performance degradataion.

> 2) How do we use an "if construct"/if clause within a SQL statement?
> Can we use conditional checking with the if construct within a stored
> procedure? Can you please post a trivial example of a stored procedure
> with an if clause?

Within an SQL statement you use the CASE expression for conditional
checking. For control-of-flow in a stored procedure you use IF. For examples
on CASE, see Books Online.

> 3) Stored procedures can have input parameters as well as output
> parameters. Can they also have in/out parameters that are like "by
> reference" parameters? What's the syntax on Microsoft's T-SQL version?

Stored procedures has input parameters and input/output parameters. They
don't have output-only parameters like Ada. You cannot control whether a
value is passed by value of by reference. (And it would not be meaningful
in a client-server interface for that matter.)

Beware that for an OUTPUT parameter, the OUTPUT keyword must be specified
both for the formal parameter and the actual parameter.

> 4) How does one check the return value of a stored procedure?

EXEC @err = some_sp
SELECT @err = coalesce(nullif(@err, 0), @error)
IF @err <> 0
-- error handling.

Return values for stored procedures is normally only used to indicate
success/failure, with 0 meaning success, and everything else failure.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]