Programming in MS SQL Server 2012

Wildcard Parameter Data



The following example describes CREATE PROCEDURE statement using the minimum required syntax with one INPUT parameters to return specified employee(s) information based on given parameter unique pattern data value. This stored procedure uses the parameter with the LIKE keyword. The parameter data value may contain wildcard characters (% or _ or [ ] and [^])

-- Example 110 --

 

USE EBusiness

GO

 

IF EXISTS (SELECT *

           FROM sysobjects

           WHERE name = N'dbo.usp_EmployeePhoneList' AND type = 'P')

           DROP PROCEDURE dbo.usp_EmployeePhoneList

GO

 

CREATE PROCEDURE dbo.usp_EmployeePhoneList

@Phone varchar(50)

AS

SELECT

   EMP.Emp_Code,

   EMP.Emp_Name,

   EMP.Phone,

   EMP.Dept_Code,

   DEPT.Dept_Name FROM EMP

INNER JOIN DEPT

ON EMP.Dept_Code = DEPT.Dept_Code

WHERE EMP.Phone LIKE @Phone


Query Output Screen



The following Transact-SQL statements are used to retrieve employee(s) information based on given input parameter unique pattern data values.

-- Example 111 --

 

EXECUTE EBusiness.dbo.usp_EmployeePhoneList '%'

 

EXECUTE EBusiness.dbo.usp_EmployeePhoneList '10%'

 

EXECUTE EBusiness.dbo.usp_EmployeePhoneList '104%'


Query Output Screen



* * * * *


Email Your Comment To AUTHOR