.Net Data Access
If you are going to be using SQL Server, you are probably using it with Microsoft .net development tools. The data access components in .Net grew up from their VB roots and are powerfull, flexible and easy to use. Unfortunatly, like most other things, they can be abused as well.

Fortunatly, Microsoft and it's community have developed some seriously idiot proof tools. The Microsoft Data Access Application Block and the Enterprise Library make calling stored procedures as easy as pie. If you are writting data access routines in C# or Visual Basic and are not using these then you should really have a good look. I see to many people creating data adaptors, commands and connections in code when they are all wrapped up in these libraries. They can be found at Application Blocks.

The other tool of note is the built in dataset designer in Visual Studio. By simply dragging tables from there server explorer to a dataset it creates a nice, type-safe dataset including table adaptors. Table adaptors as generated by Visual Studio are great although they are much more geared to Client-Server 2-tier development and not proper 3-tier or web development. Having said that they can still be used for this and are quite neat.

Optional items in a Where clause

Here is a common situation. You have a query that has several possible but optional filters. For example you may filter on the first name or last name of both. Now to do this there are several possible technics. You could
1. assemble a dynamic SQL statement, you could
2. use the if statment to execute a different select statement or
3. use conditions in the where clause to handle it in one statement

Dynamic SQL
CREATE PROCEDURE Dynamic_SQL @FirstName nvarchar(100), @Lastname nvarchar(100)

AS

DECLARE @sql nvarchar(1000)

SELECT @sql = 'SELECT * FROM Users u WHERE 1=1 '

IF @FirstName IS NOT NULL

BEGIN

SELECT @sql = @sql + ' AND FirstName =''' + @Firstname + ''''

END



IF @LastName IS NOT NULL

BEGIN

SELECT @sql = @sql + ' AND LastName =''' + @Lastname + ''''

END



print @SQL

EXEC sp_executesql @sql


The above proc does what it is supposed to do but it has a couple of drawbacks. Firstly it lacks elegance. Secondly it totally defeats the whole purpose of using a stored procedure. The dynamic SQL requires a compile, there is a significant security risk and lastly it is really hard to follow. If you are going to do this you might as well just do it in the compiled VB/C# code.

Use a Where Clause
CREATE PROCEDURE IF_SQL @FirstName nvarchar(100), @Lastname nvarchar(100)

AS

IF @FirstName IS NOT NULL AND @LastName IS NOT NULL

BEGIN

SELECT * FROM Users u WHERE FirstName = @FirstName AND LastName = @LastName

END



IF @FirstName IS NULL AND @LastName IS NOT NULL

BEGIN

SELECT * FROM Users u WHERE LastName = @LastName

END



IF @FirstName IS NOT NULL AND @LastName IS NULL

BEGIN

SELECT * FROM Users u WHERE FirstName = @FirstName

END



IF @FirstName IS NULL AND @LastName IS NULL

BEGIN

SELECT * FROM Users u

END


Again this works but, as you can imagine, any more than two paramaters and it get unwieldy. Also, the if clause can force a recompile.


WHERE Clause
ALTER PROCEDURE Where_SQL @FirstName nvarchar(100), @Lastname nvarchar(100)

AS

SELECT * FROM Users u

WHERE (FirstName = @FirstName OR @FirstName IS NULL)

AND (LastName = @LastName OR @LastName IS NULL)




Again this works and is a much more elagant solution. The code is tight, easy to follow and performs well. The main problem with this is that the compiler will factor in all of the where conditions into it's execution plan. That is, if there are tables needed only for the where clause, they will be processed even if that where condition is not needed. However, IMHO this is the way to do it for 90% of cases.