Programming in MS SQL Server 2012 |
Wildcard Parameter DataThe 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 --
Example 111 -- EXECUTE EBusiness.dbo.usp_EmployeePhoneList '%' EXECUTE EBusiness.dbo.usp_EmployeePhoneList '10%' EXECUTE EBusiness.dbo.usp_EmployeePhoneList '104%' |
* * * * *