SQL Tips
---------

This document contains various sql tips that I find useful / can't be bothered to remember properly.

-----------------------------------------------------------

How to subtract one table from another?
---------------------------------------

Use a left join...

i.e. If the query to find out what games a player has is:

        SELECT GamesForPlayers.GameID, Games.GameName FROM GamesForPlayers, Games
        WHERE PlayerID = @playerID AND Games.GameID = GamesForPlayers.GameID
        ORDER BY Games.GameName

Then the query to find out what games the player doesn't have is:

	SELECT Games.GameID, Games.GameName FROM Games
	LEFT JOIN GamesForPlayers
	ON Games.GameID = GamesForPlayers.GameID
	WHERE GamesForPlayers.GameID is NULL
	ORDER BY Games.GameName

-----------------------------------------------------------


RAISERROR
---------

Don't forget to return after a RAISERROR has been triggered...

i.e.

        -- validate the player name is not null or empty
	IF LEN(@playerName) = 0
	BEGIN
		RAISERROR('No player name specified.', 11, 1)
		RETURN 1
	END

And if calling a stored procedure that uses RAISERROR - don't forget to check and return there too

-----------------------------------------------------------


Indexes
-------

Put indexes on things you typically sort by, i.e. GameName, PlayerName, etc
GameName/PlayerName can't be varchar(MAX) - it has to be varchar(512) or nvarchar(256) (unicode)

-----------------------------------------------------------


Callback
--------

-- declare the callback method to use...
DECLARE @callbackName varchar(MAX)
SELECT @callbackName = '[dbo].[GetGames]'

-- execute the callback method and store the result in @return_value
DECLARE	@return_value int
EXEC	@return_value = @callbackName

-----------------------------------------------------------