Skip Navigation Links.
A Stored Procedure to Look Up Names
Language(s):SQL Server
Category(s):Query
This article shows how to write a stored procedure to retrieve names on Last, First, Middle, SSN, DOB or any combination. This is a companion article to a video on the Skycoder Channel: www.youtube.com/c/SkycoderChannel

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

This article has been viewed 3499 times.
The examples on this page are presented "as is". They may be used in code as long as credit is given to the original author. Contents of this page may not be reproduced or published in any other manner what so ever without written permission from Idioma Software Inc.