저장프로시져

로그인

 

ALTER PROCEDURE dbo.LogMember                (              @m_id varchar(50),              @m_pw varchar(50),              @result int output              ) AS    declare @existed int    declare @state int    Exec AvailId @m_id, @m_pw, @existed output              If @existed = 0  /*ID 와PW가일치안한다면*/              begin                            set @result = 0                             end              else     /*일치한다면*/                begin                    set @state = ( select  state  from MemberInfo where m_id=@m_id )       /*state를조사한다.  */                                             if @state = 0    /* 이미로그인중이라니라면상태를1로변환한다. */                                             begin                                          set @result = 1                                              update MemberInfo set state=1 where m_id=@m_id                                              update FriendList set state =1 where m_id = @m_id                                                                               /*state를1로변환시켜준다. */                    end                                 else                                          begin                                                     set @result = 0  /*아이디를있지만이미로그인상태이다.*/                                         end                                              end              RETURN

 

 

 

AvailId

아디가 유효한지 확인

ALTER PROCEDURE dbo.AvailId              (                @m_id varchar(50),                @m_pw varchar(50),                @result int output              )              AS                if Exists (select m_id from MemberInfo where m_id=@m_id and m_pw =@m_pw)                           begin                 set @result = 1    end       else      begin         set @result = 0    end    RETURN

 

 

시청 동영상 목록 요청

 

ALTER PROCEDURE dbo.ListVideo ( @m_id varchar(50), @count int output ) AS              declare @sql nvarchar(1000)              set @count = 0              Exec GetVideoListCount @m_id,@count output                           if @count !=0                           begin                                set @sql = 'select m_videoname from '+@m_id+'_videoInfo'                            exec (@sql)                                                       end                     else                            begin                            set @count = 0                            end                                  RETURN

 

해당 ID의 시청한 동영상의 개수

ALTER PROCEDURE dbo.GetVideoListCount              (              @m_id varchar(50),              @count int output                         ) AS              declare @sql nvarchar(1000)              declare @p nvarchar(100)              declare @cnt int                           set @sql = 'select @cnt = count(m_videoname) FROM '+@m_id+'_videoInfo'               set @p = '@cnt int output'                           exec sp_executesql @sql,@p, @cnt=@count output                             RETURN

 

시청 동영상별 저장 단어와 뜻 요청

ALTER PROCEDURE dbo.ListWord ( @m_id varchar(50), @m_videoname varchar(50), @count int output ) AS              declare @sql nvarchar(1000)              declare @param nvarchar(1000)              set @count = 0              Exec GetWordListCount @m_id,@m_videoname,@count output                           if @count !=0                            begin                                                                                                                 set @sql = 'select m_word,m_mean from '+@m_id+'_wordInfo where m_videoname =@p_videoname'                                 set @param = '@p_videoname varchar(50)'                                                                                 execute sp_executesql @sql,@param,                                                                                                                                                                  @p_videoname = @m_videoname                                                                                end                     else                            begin                            set @count = 0                            end                                  RETURN

 

영상의 단어 개수 확인

ALTER PROCEDURE dbo.GetWordListCount              (              @m_id varchar(50),              @m_videoname varchar(50),              @count int output                         ) AS              declare @sql nvarchar(1000)              declare @p nvarchar(100)              declare @cnt int                           set @sql = 'select @cnt = count(m_word) FROM '+@m_id+'_wordInfo where m_videoname = @p_videoname'                  set @p = '@cnt int output,                                                                   @p_videoname varchar(50)'                           exec sp_executesql @sql,@p,                                                                                               @p_videoname = @m_videoname,                                                                                              @cnt=@count output                             RETURN