Stored procedures... 

Basic syntax:

CREATE PROCEDURE getNumPeaks

/* list params */
@whichRegion VARCHAR (8000) = NULL

AS BEGIN

DECLARE @queryString VARCHAR (8000);

SET @queryString = 'SELECT COUNT(*) FROM peak' + 
ISNULL(' WHERE region = ''' + @whichRegion + '''', ' ');

EXECUTE (@queryString);
END;
GO

EXECUTE getNumPeaks
	@whichRegion = 'Corocoran to Whitney';
GO

To return the number of peaks:

CREATE PROCEDURE getNumPeaks
@whichRegion VARCHAR (8000),
@result INT OUTPUT
AS BEGIN

DECLARE myRes CURSOR FOR
SELECT COUNT(*) FROM peak WHERE region = '''' + @whichRegion + '''';

OPEN myRes;
FETCH myRes INTO @result;
CLOSE myRes;
DEALLOCATE myRes;
GO 

DECLARE @myResult INT
EXECUTE getNumPeaks
	@whichRegion = 'Corocoran to Whitney',
	@result = myResult;
PRINT @myResult;
GO

This illustrates a cursor, which allows you to loop thru query results.  Ex:

CREATE PROCEDURE getTallestPeak
@whichRegion VARCHAR (8000),
@result VARCHAR (8000) OUTPUT
AS BEGIN

DECLARE @peakName VARCHAR (8000);
DECLARE @bestName VARCHAR (8000);
DECLARE @peakHeight INT;
DECLARE @bestHeight INT;

DECLARE myRes CURSOR FOR
SELECT name, elev FROM peak WHERE region = '''' + @whichRegion + '''';

SET @bestHieght = -1;
OPEN myRes;
FETCH myRes INTO @peakName,  @peakHeight;

WHILE (@@FETCH_STATUS = 0) BEGIN
	IF @peakHeight > @bestHeight BEGIN
		@bestHeight = @peakHeight;
		@bestName = @peakName;
	END
	FETCH myRes INTO @peakName,  @peakHeight;
END
CLOSE myRes;
DEALLOCATE myRes;

SET @result = @bestName;
END;
GO

DECLARE @myResult VARCHAR (8000);
EXECUTE getTallestPeak;
	@whichRegion = 'Corocoran to Whitney',
	@result = myResult;
PRINT @myResult;
GO

Key cursor facts:
@@CURSOR_ROWS
@@FETCH_STATUS

Can do FETCH_RELATIVE 3 to jump 3 slots

Can have cursor variables... 

DECLARE @myRes CURSOR;

DECLARE optionOne CURSOR FOR
SELECT name, elev FROM peak WHERE region = '''' + @whichRegion + '''';
DECLARE optionTwo CURSOR FOR
SELECT name, elev FROM peak;

IF @whichRegion IS NULL
	SET @myRes = optionTwo;
ELSE
	SET @myRes = optionOne;


Can use cursor var to have truely dynamic cursors:

DECLARE @myRes CURSOR;
DECLARE @cursorText VARCHAR (8000);

SET @cursorText = 'DECLARE @myRes CURSOR FOR 
	SELECT COUNT(*) FROM peak' + 
	ISNULL(' WHERE region = ''' + @whichRegion + '''', ' ');
EXECUTE (@cursorText);

Note: SQL server has NO arrays, linked lists, trees, etc.
Only data structure is a table.  Just use it like an array!

Note: # prefix in temp table means local, ## means global
CREATE TABLE #myArray (index INT, value INT);