Some basic questions

[Water Cooler v2]

I've not touched SQL server programming since 1999. I have very little
memory of it and need some clarifications on some basic questions that
I could even use a book for. Until I get myself a good book, someone
please help me with the answers:
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?

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?

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?

4) How does one check the return value of a stored procedure?
Thanks for helping out.

[Erland Sommarskog]

Water Cooler v2 (wt*****@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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Water Cooler v2]

Thank you very much, Erland and SriSamp. Some extra bit of thanks to
Erland for taking out the time for custom-answers to my questions.