CREATE TABLE dbo.tbllb_DBoard (
idx INT NOT NULL,
lb_name VARCHAR(10) NOT NULL,
lb_SCate TINYINT NOT NULL CONSTRAINT DF_tbllb_DBoard_lb_SCate DEFAULT (0),
lb_news BIT NOT NULL,
m_id VARCHAR(20),
Umame VARCHAR(20) NOT NULL,
eMail VARCHAR(50),
Title VARCHAR(100) NOT NULL,
TagFlag BIT NOT NULL,
Content TEXT NOT NULL,
pwd VARCHAR(10),
readNum INT NOT NULL CONSTRAINT DF_tbllb_DBoard_readNum DEFAULT (0),
rRef SMALLINT,
rLevel SMALLINT,
rStep SMALLINT,
file1 VARCHAR(50),
file1_size INT,
file1_width INT,
file2 VARCHAR(50),
file3 VARCHAR(50),
writeday DATETIME NOT NULL CONSTRAINT DF_tbllb_DBoard_writeday DEFAULT (getdate()),
CONSTRAINT idx_board
PRIMARY KEY NONCLUSTERED (idx ASC, lb_news DESC, rRef DESC, rStep ASC) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE dbo.tbllb_DBoard
WITH CHECK ADD CONSTRAINT FK_tbllb_DBoard_tbllb_config
FOREIGN KEY ( lb_name )
REFERENCES dbo.tbllb_config ( lb_name )
;
CREATE PROCEDURE [Proc_lb_DList]
@lb_name VarChar(10),
@rlb_SCate VarChar(50),
@SearchF VarChar(30),
@SearchS VarChar(50),
@npage INT,
@lb_CateName VarChar(50) OUTPUT,
@lb_Nickname VarChar(50) OUTPUT,
@lb_open Char(1) OUTPUT,
@lb_design Char(28) OUTPUT,
@lb_record INT OUTPUT,
@lb_width VARCHAR(5) OUTPUT,
@szRecordCount INT OUTPUT,
@szPageCount INT OUTPUT,
@MsgFlag SMALLINT OUTPUT,
@Msg VARCHAR(50) OUTPUT
AS
DECLARE @sql varchar(8000)
DECLARE @sql2 varchar(1000)
DECLARE @sql3 nvarchar(4000) ---NvarChar형식이여야 함.
DECLARE @p nvarchar(100)
DECLARE @sum_cnt INT -- output 파라메터를 위한 선언
SET @sql=''
SET @sql2=''
SET @SearchS=rTrim(ltrim(@SearchS))
IF @rlb_SCate<>99 BEGIN
Set @sql2=@sql2 + ' and ((lb_news=0 and lb_SCate=' + Convert(Varchar(5),@rlb_SCate) + ') or lb_news=1)'
END
IF @SearchS<>'' BEGIN
IF @SearchF='Title' BEGIN
SET @sql2=@sql2 + ' and ((Title like ' +Char(39) + '%' + @SearchS + '%' + Char(39) + ') or (Umame like ' +Char(39) + '%' + @SearchS + '%' + Char(39) + '))'
END
ELSE BEGIN
SET @sql2=@sql2 + ' and Content like ' +Char(39) + '%' + @SearchS + '%' + Char(39)
END
END
IF EXISTS(select lb_name from tbllb_config where lb_name=@lb_name)
BEGIN
Set @lb_CateName=(select lb_CateName from tbllb_Cate where lb_Cate=(select lb_Cate from tbllb_config where lb_name=@lb_name))
Set @lb_Nickname=(select lb_Nickname from tbllb_config where lb_name=@lb_name)
Set @lb_open=(select lb_open from tbllb_config where lb_name=@lb_name)
Set @lb_design=(select lb_design from tbllb_config where lb_name=@lb_name)
Set @lb_record=(select lb_record from tbllb_config where lb_name=@lb_name)
Set @lb_width=(select lb_width from tbllb_config where lb_name=@lb_name)
SET @sql = 'select top ' + convert(varchar(10),@lb_record) + ' idx, lb_news, m_id, Umame, eMail, lb_SCate,
(case lb_news when 1 then ' + Char(39) +Char(39) + ' else (case lb_SCate when 0 then (case when (select count(lb_SCate) from tbllb_SCate where lb_Name=tbllb_Dboard.lb_Name)>0 then ' + Char(39) + '[기타]' +Char(39) +' else ' + Char(39) +Char(39) +' end) else (select ' + Char(39) + '[' + Char(39) + '+lb_SCateName+' + Char(39) + ']' + Char(39) + ' from tbllb_SCate where lb_Name=tbllb_Dboard.lb_Name and lb_SCate=tbllb_Dboard.lb_SCate) end) end ) as lb_SCateName,
Title,readNum, convert(varchar(10),writeday,121) as writeday, (case when convert(varchar(10),writeday,121)>=convert(varchar(10),getdate()-1,121) then 1 else 0 end) as date_flag, rLevel,(case when (file1_width>0 and file1<>' + char(39)+char(39)+ ') then 1 when file1<>' + char(39)+char(39)+ ' then 2 else 0 end) as file_flag, file1,(select count(num) from tbllb_reView where lb_name=' + char(39) + @lb_name + char(39) + ' and idx=tbllb_Dboard.idx) as reViweNO from tbllb_Dboard where lb_name=' + char(39) + @lb_name + char(39) + @sql2
SET @sql = @sql + ' and idx Not in(select top ' +convert(varchar(10),@lb_record*(@npage-1)) + ' idx from tbllb_Dboard where lb_name=' + char(39) + @lb_name + char(39) + @sql2 + ' Order by lb_news Desc, rRef desc, rStep asc) Order by lb_news Desc, rRef desc, rStep asc'
-- 주문 수량 카운트
SET @sql3='select @sum_cnt_in=(count(idx)) from tbllb_DBoard where lb_name=' +char(39) + @lb_name + char(39) + @sql2
set @p = '@sum_cnt_in INT OUTPUT'
EXEC sp_executesql @sql3, @p, @sum_cnt_in=@sum_cnt OUTPUT
SET @szRecordCount=@sum_cnt
SET @szPageCount=(CONVERT(INT,((@szRecordCount - 1)/@lb_record) + 1))
--select @sql
exec(@sql)
SET @MsgFlag=1 -- 1 성공
END
ELSE BEGIN
SET @MsgFlag=0 -- 1저장 실패
SET @Msg='잘못 된 접근입니다.'
END
CREATE PROCEDURE [Proc_lb_BregProc]
@lb_name VarChar(10),
@lb_SCate tinyInt,
@idx INT,
@rRef SmallINT,
@rLevel SmallINT,
@rStep SmallINT,
@flag Char(1),
@Umame VarChar(10),
@M_id VarChar(20),
@Title VarChar(100),
@eMail VarChar(50),
@Content TEXT,
@TegFlag BIT,
@lb_news BIT,
@Pwd VarChar(10),
@MsgFlag SmallINT OUTPUT,
@Msg VarChar(50) OUTPUT,
@NextIdx INT OUTPUT
AS
set nocount on
DECLARE @regFlag CHAR(1)
IF EXISTS(select lb_name from tbllb_config where lb_name=@lb_name)
BEGIN
SET @regFlag=(select lb_open from tbllb_config where lb_name=@lb_name)
SET @NextIdx=(select isnull(MAX(idx),0)+1 from tbllb_Bboard)
Set @M_id=rtrim(@M_id)
Set @Pwd=rtrim(@Pwd)
IF @flag='R' and @idx>0 BEGIN
SET @lb_SCate=(select top 1 lb_SCate from dbo.tbllb_Bboard where rRef=@rRef Order by rLevel Asc)
UPdate tbllb_Bboard Set rStep=rStep+1 Where lb_name=@lb_name and rRef=@rRef and rStep>@rStep
SET @rStep=@rStep+1
SET @rLevel=@rLevel+1
END
ELSE BEGIN
SET @rRef=@NextIdx
SET @rStep=0
SET @rLevel=0
END
IF @regFlag='W' or LEN(@M_id)>0 BEGIN
INSERT INTO tbllb_Bboard
(idx,lb_name,lb_SCate,lb_news, m_id,Umame,eMail,Title, TagFlag, Content, pwd, rRef, rLevel, rStep)
values
(@NextIdx,@lb_name,@lb_SCate,@lb_news, @M_id,@Umame,@eMail,@Title, @TegFlag, @Content, @Pwd, @rRef, @rLevel, @rStep)
SET @MsgFlag=1 -- 1저장 성공
END
ELSE BEGIN
SET @MsgFlag=0 -- 1저장 성공
SET @Msg='게시판 쓰기 권한이 존재하지 않습니다.' -- 1저장 성공
END
END
ELSE BEGIN
SET @MsgFlag=0 -- 1저장 성공
SET @Msg='잘못 된 접근입니다.' -- 1저장 성공
END
set nocount off'DB > MSSQL' 카테고리의 다른 글
| [MSSQL] 프로시져3 (0) | 2022.04.26 |
|---|---|
| [MSSQL] 프로시져2 (0) | 2022.04.26 |
| [MSSQL]컬럼 목록 및 테이블 리스트 (0) | 2022.04.21 |
| [MSSQL]프로시져1 (0) | 2022.04.21 |
| [MSSQL] 날짜 형식 맞추기 (0) | 2022.04.21 |
마크제이콥스
초보 개발자의 이슈및 공부 내용 정리 블로그 입니다.