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

IF EXISTS (SELECT *

FROM sysobjects

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

DROP PROCEDURE dbo.usp_EmployeePhoneList

CREATE PROCEDURE dbo.usp_EmployeePhoneList

@Phone varchar(50)

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

* * * * *

Your Comment To AUTHOR