Code a stored procedure that get one single record or all records from a table

Question

I'm searching for the cleanest solution here. I would like to code a stored procedure that should retrieve one record if the proper key is passed in input, or all the records if it is called without parameter.

Table FOO has two fields, CODE and DESCRIPTION. In Sql server 2008, i usually create a stored procedure with like this:

CREATE PROCEDURE getFoo 
@CODE CHAR(3)=NULL
AS
IF @CODE is NULL 
  BEGIN
    SELECT CODE,DESCRIPTION FROM FOO
  END
ELSE
  BEGIN
    SELECT CODE,DESCRIPTION FROM FOO WHERE CODE=@CODE
  END
GO

The main problem with my solution is obviously the repetition of the SELECT that could be painful if table has many fields or has many joins to other tables.

Another approach that i see in others SP is:

CREATE PROCEDURE getFoo 
@CODE CHAR(3)=NULL
AS
DECLARE @FILTER CHAR(3)
SET NOCOUNT ON
IF @CODE is NULL SET @Filtro='%'
ELSE SET @FILTER =@CODE 

SELECT CODE,DESCRIPTION
FROM FOO
WHERE
CODE like @FILTER
GO

What is your preferred code for this simple task? Do you construct it dinamically and use EXECUTE at the end? thanks

Answer 1

SELECT CODE,DESCRIPTION 
FROM FOO 
WHERE CODE=@CODE OR @CODE IS NULL

Answer 2

I would do either what you did, or sometimes (depending on my mood) just:

SELECT CODE,DESCRIPTION FROM FOO WHERE CODE=ISNULL(@CODE,CODE)

I would NOT recommend building a dynamic string and executing it, here or anywhere else it can be avoided. It may have performance implications (but I'm not sure how true this is anymore), it make it hard to read, you get less compile-time syntax validation when you compile the proc, you get less useful information about the system dependencies, and (worst of all) you can open yourself up to SQL-injection attacks.

Answer 3

From your code, I'm guessing TSQL, so here is a very complete and quite extensive article on Dynamic Search Conditions in T-SQL by Erland Sommarskog. It will show that there is no one correct way, it depends on many factors.

It covers all the PROs and CONs of every possible method: http://www.sommarskog.se/dyn-search.html