Here is an example of a stored procedure to retrieve names
by Last, First, Middle, SSN and DOB.
The table used in this sproc was generated randomly using
the techniques outlined in this article: http://www.skycoder.com/Articles/Article_90/
Watch the video: http://youtu.be/zI2PCGyqMIE
Watch the video showing how to create the database: https://www.youtube.com/watch?v=4pK_D3oX72Y
--
=============================================
-- Author: Jon Vote
-- Create date: 2015-01-26
-- Description: Names Lookup
--
=============================================
Create PROCEDURE [dbo].[GetNames]
@LastName nvarchar(50) = null,
@FirstName nvarchar(50) = null,
@MiddleInitial char = null,
@SSN varchar(11) = null,
@DOB DateTime = null,
@NumRecords int = 1000
AS
BEGIN
SET NOCOUNT ON;
Select Top(@NumRecords)
Id,
FirstName,
LastName,
MiddleInitial,
Gender,
SSN,
DOB
From Names
Where (@LastName is null or LastName like @LastName)
And (@FirstName is null or FirstName like @FirstName)
And (@MiddleInitial is null or MiddleInitial = @MiddleInitial)
And (@SSN is null or SSN = @SSN)
And (@DOB is null or DOB = @DOB)
Order By LastName, FirstName, MiddleInitial, DOB
END