[ms-sql] 서브스트링(substring), 프로시저(SP) 작성 예제

create proc dbo.mo_exam4_2007
as
/*

    1) 시스템    : Database 테스트
    2) 기능     : mo_exam3_2007 테이블에서 birthyear, count를 출력하는 SP
    3) 입력     :  없음
    4) 작성자    : picomax
    5) 작성일자 : 2007.03.23
    6) 수정내역
    7) Test Example
         Exec mo_exam4_2007
*/
select
    convert(varchar(4),birthday) as BIRTHYEAR,
    count(birthday) as NO
from
    mo_exam3_2007 group by convert(varchar(4),birthday)

execute mo_exam4_2007


select * from mo_exam3_2007

exec mo_exam5_2007 '', '',  '', '', '857'

alter proc dbo.mo_exam5_2007
    @name    VARCHAR(30),    -- 이름
    @address    VARCHAR(50),    -- 이름
    @tel1 VARCHAR(4),    -- 전화번호1
    @tel2 VARCHAR(4),    -- 전화번호2
    @tel3 VARCHAR(4)    -- 전화번호3
AS

set nocount on

set @name = '%'+@name+'%'
set @address = '%'+@address+'%'
set @tel1 = '%'+@tel1+'%'
set @tel2 = '%'+@tel2+'%'
set @tel3 = '%'+@tel3+'%'


create table #tmp (
    name    varchar (30),
    part    varchar(50),
    birthday    varchar(10),
    hireday    varchar(10),
    address    varchar(50),
    city    varchar(30),
    country    varchar(20),
    tel1    varchar(4),
    tel2    varchar(4),
    tel3    varchar(4)
)

delete from #tmp

insert into #tmp

select 
    name,
    part,
    birthday,
    hireday,
    address,
    city,
    country,
    substring(tel,2,(charindex(')',tel)-2)) as tel1,
    substring(tel,charindex(')',tel)+2,3) as tel2,
    substring(tel,charindex('-',tel)+1,4) as tel3
from mo_exam3_2007

select 
    name,
    part,
    birthday,
    hireday,
    address,
    city,
    country,
    tel1,
    tel2,
    tel3
from #tmp
where
    name like @name
    and address like @address
    and tel1 like @tel1
    and tel2 like @tel2
    and tel3 like @tel3