Simple Stored Procedure to perform various select operations

Note : Stored Procedure below uses same Users table in my earlier Post .

• Following stored procedure helps to perform various select operation on Users table (Refer Note).
• @Function Parameter will help us to decide the search criteria, that is kind of search that we need to perform on our Users table. 
• We have used one parameter as @Name  instead two different parameters for First_Name and  Last_Name. This way we will check whether entered search @Name field matched with either First_Name  or Last_Name.
• We have used few simple search criteria in this stored procedure to show SQL Functions : LIKE ,OR,  AND 
• Various Searches performed are as follows
• SELECTALL Show All records. 
• Name First Name or Last Name starts with user entered @Name field.
• EMail Email contains field @Email
• NameCity User with First Name or Last Name (@Name) from a specific city field  (@City)

CREATE PROCEDURE [dbo].[SP_User_GetData]

      -- Add the parameters for the stored procedure here

      @Function VARCHAR(100),@Name VARCHAR(100),@Email VARCHAR(100),

      @City VARCHAR(100)

AS

BEGIN

      

      -- SET NOCOUNT ON added to prevent extra result sets FROM

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

      

      -- Insert statements for procedure here

      -- Show all Users : This is default Search, sort by User_ID

      IF @Function = 'SELECTALL'

      BEGIN

            SELECT U.User_ID,U.First_Name,U.Last_Name,U.Email,U.City,U.Phone

            FROM dbo.Users U

            ORDER BY U.User_ID;

      END

     

      

     --Show user with First Name or Last Name search criteria, sorting by First

     -- Name followed by Last Name

      IF @Function='Name'

      BEGIN

            SELECT U.User_ID,U.First_Name,U.Last_Name,U.Email,U.City,U.Phone

            FROM dbo.Users U

            WHERE U.First_Name LIKE '%'+@Name+'%' OR U.Last_Name LIKE       

            '%'+@Name+'%'

            ORDER BY U.First_Name, U.Last_Name;

      END

     

     

      --Show users with Email search criteria, Sorting result with Email

      IF @Function='EMail'

      BEGIN

            SELECT U.User_ID,U.First_Name,U.Last_Name,U.Email,U.City,U.Phone

            FROM dbo.Users U

            WHERE U.Email LIKE '%'+@Email+'%'

            ORDER BY U.Email;

      END

     

      -- Search by Name (First Name / Last Name) and City

      IF @Function='NameCity'

      BEGIN

            SELECT U.User_ID,U.First_Name,U.Last_Name,U.Email,U.City,U.Phone

            FROM dbo.Users U

            WHERE U.City LIKE  @City

            AND (U.First_Name LIKE @Name+'%' OR U.Last_Name LIKE @Name+'%')

            ORDER BY U.User_ID;

      END

     

END

Stored Procedure Execution 

Using various search criteria with @Function parameter mentioned in stored procedure above.

• Using SELECTALL to show all records in Users table 

DECLARE     @return_value int

EXEC  @return_value = [dbo].[SP_User_GetData]

            @Function = N'SELECTALL',

            @Name = N'  ',

            @Email = N' ',

            @City = N' '

SELECT      'Return Value' = @return_value

GO

• Using Name to show First Name or Last Name specific records in Users table 

DECLARE     @return_value int

EXEC  @return_value = [dbo].[SP_User_GetData]

            @Function = N'Name',

            @Name = N'P',

            @Email = N' ',

            @City = N' '

SELECT      'Return Value' = @return_value

GO

• Using EMail to show  Email specific records in Users table 

DECLARE     @return_value int

EXEC  @return_value = [dbo].[SP_User_GetData]

            @Function = N'EMail',

            @Name = N' ',

            @Email = N'xyz',

            @City = N' '

SELECT      'Return Value' = @return_value

GO

• Using NameCity to show users with either specific First Name or Last Name belonging to specific city records in Users table 

DECLARE     @return_value int

EXEC  @return_value = [dbo].[SP_User_GetData]

            @Function = N'NameCity',

            @Name = N'P',

            @Email = N' ',

            @City = N'City1'

SELECT      'Return Value' = @return_value

GO