18_회원

CREATE TABLE MEMBER3(
IDX INT IDENTITY
,NO INT         --번호
,ID VARCHAR(20)    --아이디
,NAME VARCHAR(20)  --이름
,PHONE VARCHAR(20)  --연락처
)
SELECT * FROM MEMBER3 order by idx desc
use mydb

select * from tb_guest

insert into tb_guest(subject) values('행복하세요')

create table tb_member
(
 member_idx int      identity,
 uid      varchar(20) primary key,
 upw     varchar(10) not null,
 uname    varchar(20) not null,
 jumin1    char(6)    not null,
 jumin2    char(7)    not null,
 uzip     char(7)    not null,
 uaddr     varchar(255),
 uphone    varchar(20),
 uemail    varchar(50),
 regdt     datetime   default getdate()
)

 

*회원들이 새로운 클럽을 만들면 생성되는 SP
스키동호회

declare @BoardName varchar(50)
declare @sql varchar(255)
set @BoardName='스키동호회'
set @sql='create table '+@BoardName
set @sql=@sql+'(bnum int,bname varchar(10))'
exec (@sql)

alter proc CreateCafe
 @BoardName varchar(50)
as
 declare @sql varchar(255)
 set @sql='create table '+@BoardName
 set @sql=@sql+'(bnum int,bname varchar(10))'
 exec (@sql)

CreateCafe '래프팅동호회'

select * from 래프팅동호회

 

select * from 스키동호회

 

*게시판 이름을 입력하면 새로운 게시판이
  만들어지는 저장 프로시저

 

create proc Make_myboard
 @BoardName varchar(50)
as
 declare @sql varchar(255)
 set @sql='create table '+@BoardName
 set @sql=@sql+'(bno int identity primary key,'
 set @sql=@sql+'subject varchar(255) not null,'
 set @sql=@sql+'contents text not null,'
 set @sql=@sql+'readcnt int default 0,'
 set @sql=@sql+'regnm varchar(20) not null,'
 set @sql=@sql+'regdt datetime default getdate())'
   exec (@sql) 

Make_myboard  '수다들의모임'
select * from 수다들의모임
[회원인증 프로시저]
존재의 유무
(커버드쿼리라 함:조회의 대상과 결과의 대상이 같은것)
select * from tb_member
alter proc sp_MemberLogin
 @id varchar(20),@pw varchar(20)
as
 if exists(select uid from tb_member where uid=@id and upw=@pw)
  select 'O' as [res]
   else
  select 'X' as [res]

sp_MemberLogin 'lee', '1234'