USE [BOARD]
GO
/****** Object: StoredProcedure [dbo].[SP_BOARD2] Script Date: 04/07/2011 19:12:44 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER on
GO
/*****수정 삭제*****/
ALTER PROCEDURE [dbo].[SP_BOARD2]
@num INT = default,
@title VARCHAR(100),
@writer VARCHAR(20),
@email VARCHAR(100),
@contents VARCHAR(MAX),
@ip_add VARCHAR(10),
@parent INT = default
AS
SET NOCOUNT on;
IF @num IS NULL
BEGIN
DECLARE @grp AS INT,
@seq_num AS INT,
@indt AS INT
IF @parent IS NULL
BEGIN
SELECT @grp = ISNULL(MAX(grp), 0) + 1
FROM BOARD2
END
ELSE
BEGIN
DECLARE
@p_parent AS INT,
@p_seq AS INT,
@max_seq AS INT
SELECT
@grp = grp,
@indt = indt + 1,
@p_seq = seq_num,
@p_parent = parent
FROM BOARD2
WHERE num = @parent
SELECT @max_seq = MAX(seq_num) + 1
FROM BOARD2
WHERE grp = @grp
DECLARE
@tmp_parent AS INT,
@tmp_p_seq AS INT,
@tmp_p_parent AS INT
SET @tmp_parent = @parent
SET @tmp_p_seq = @p_seq
SET @tmp_p_parent = @p_parent
WHILE @tmp_p_parent > 0
BEGIN
SELECT @seq_num = MIN(seq_num)
FROM BOARD2
WHERE grp = @grp
AND parent = @tmp_p_parent
AND seq_num > @tmp_p_seq
IF @seq_num IS NOT NULL BREAK
SELECT
@tmp_parent = num,
@tmp_p_seq = seq_num,
@tmp_p_parent = parent
FROM BOARD2
WHERE num = @tmp_p_parent
END
IF @seq_num IS NULL
SET @seq_num = @max_seq + 1
UPDATE BOARD2 SET
seq_num = seq_num + 1
WHERE grp = @grp AND seq_num >= @seq_num
END
INSERT INTO BOARD2 (
title, writer, email, ip_add, parent, seq_num,
indt, grp
) VALUES (
@title, @writer, @email, @ip_add, ISNULL(@parent, 0), ISNULL(@seq_num, 0),
ISNULL(@indt, 0), @grp
)
SET @num = @@IDENTITY
END
ELSE
BEGIN
UPDATE BOARD2 SET
title = @title,
writer = @writer,
email = @email,
mod_date = GETDATE(),
mod_ip = @ip_add
WHERE num = @num
UPDATE CONTENTS SET
deleted = 1,
del_date = GETDATE()
WHERE board_num = @num
END
DECLARE @first AS INT
SET @first = 1
WHILE @first <= LEN(@contents)
BEGIN
INSERT INTO CONTENTS (board_num, contents)
VALUES (@num, SUBSTRING(@contents, @first, 5));
SET @first = @first + 5
END
GO
/****** Object: NumberedStoredProcedure [dbo].[SP_BOARD2];2 Script Date: 04/07/2011 19:12:45 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER on
GO
ALTER PROCEDURE [dbo].[SP_BOARD2];2
@page INT,
@page_size INT,
@skey VARCHAR(10) = '',
@sval VARCHAR(20) = ''
AS
SET NOCOUNT on;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH LST AS (
SELECT
ROW_NUMBER() OVER (ORDER BY grp DESC, seq_num ASC) AS row_num,
num, title, writer, email, view_count, reg_date, ip_add, del_int,
parent, grp, indt, seq_num
FROM BOARD2 AS brd
INNER JOIN (
SELECT board_num
FROM CONTENTS
WHERE deleted = 0
AND CASE @skey
WHEN 'title' THEN @sval
WHEN 'contents' THEN contents
ELSE contents
END LIKE '%' + @sval + '%'
GROUP BY board_num
)AS jn
ON brd.num = jn.board_num
WHERE del_int <> 2
AND CASE @skey
WHEN 'title' THEN title
WHEN 'contents' THEN @sval
ELSE title
END LIKE '%' + @sval + '%'
)
SELECT
CONVERT(INT, (SELECT COUNT(1) FROM LST)) AS rcount,
num, title, writer, email, view_count, reg_date, ip_add, del_int,
parent, grp, indt, seq_num
FROM LST
WHERE row_num BETWEEN (@page - 1) * @page_size + 1 AND @page * @page_size
GO
/****** Object: NumberedStoredProcedure [dbo].[SP_BOARD2];3 Script Date: 04/07/2011 19:12:45 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER on
GO
ALTER PROCEDURE [dbo].[SP_BOARD2];3
@num INT,
@update BIT = 0
AS
SET NOCOUNT on;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @prev AS INT,
@next AS INT
EXEC @prev = SP_BOARD2;5 @num, 'prev'
EXEC @next = SP_BOARD2;5 @num, 'next'
IF @update = 1
UPDATE BOARD2 SET
view_count = view_count+1
WHERE num = @num
SELECT 'cont' AS gubun, num, title, writer, email, view_count, reg_date
FROM BOARD2
WHERE num = @num
UNION ALL
SELECT 'prev' AS gubun, num, title, NULL, NULL, NULL, NULL
FROM BOARD2
WHERE num = @prev
UNION ALL
SELECT 'next' AS gubun, num, title, NULL, NULL, NULL, NULL
FROM BOARD2
WHERE num = @next
SELECT contents
FROM CONTENTS
WHERE board_num = @num AND deleted = 0
ORDER BY num
GO
/****** Object: NumberedStoredProcedure [dbo].[SP_BOARD2];4 Script Date: 04/07/2011 19:12:45 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER on
GO
ALTER PROCEDURE [dbo].[SP_BOARD2];4
@num INT,
@del_ip VARCHAR(100)= 0
AS
SET NOCOUNT on;
IF EXISTS (
SELECT 1
FROM BOARD2
WHERE parent = @num
)
BEGIN
UPDATE BOARD2 SET
del_int = 1,
del_date = GETDATE(),
del_ip = @del_ip
WHERE num = @num
END
ELSE
BEGIN
UPDATE BOARD2 SET
del_int = 2,
del_date = GETDATE(),
del_ip = @del_ip
WHERE num = @num
UPDATE CONTENTS SET
deleted = 1,
del_date = GETDATE()
WHERE board_num = @num
END
GO
/****** Object: NumberedStoredProcedure [dbo].[SP_BOARD2];5 Script Date: 04/07/2011 19:12:45 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER on
GO
ALTER PROCEDURE [dbo].[SP_BOARD2];5
@num INT,
@gubun CHAR(4) = 'prev'
AS
SET NOCOUNT on;
DECLARE @rst AS INT,
@group AS INT,
@seq AS INT
--현재글의 그룹번호와 seq_num을 받는다.
SELECT @group = grp, @seq = seq_num
FROM BOARD2
WHERE num = @num
-- 이전글(최신글)에 대한 처리)
IF @gubun = 'prev'
BEGIN
DECLARE @pre_group AS INT,
@pre_seq AS INT
SELECT @rst = num
FROM BOARD2
WHERE grp = @group
AND del_int = 0
AND seq_num = (
SELECT MAX(seq_num)
FROM BOARD2
WHERE grp = @group
AND seq_num < @seq
AND del_int = 0
)
IF @rst IS NULL
BEGIN
WITH LST AS (
SELECT num, seq_num
FROM BOARD2
WHERE grp = (
SELECT MIN(grp)
FROM BOARD2
WHERE grp > @group
AND del_int = 0
) AND del_int = 0
)
SELECT @rst = num
FROM LST
WHERE seq_num = (
SELECT MAX(seq_num)
FROM LST
)
END
END
-- 다음글에 대한 처리
ELSE IF @gubun = 'next'
BEGIN
DECLARE @next_group AS INT,
@next_seq AS INT
SELECT @rst = num
FROM BOARD2
WHERE grp = @group
AND seq_num = (
SELECT MIN(seq_num)
FROM BOARD2
WHERE seq_num > @seq
AND del_int = 0
AND grp = @group
)
IF @rst IS NULL
BEGIN
WITH LST AS (
SELECT num, seq_num
FROM BOARD2
WHERE grp = (
SELECT MAX(grp)
FROM BOARD2
WHERE grp < @group
AND del_int = 0
) AND del_int = 0
)
SELECT @rst = num
FROM LST
WHERE seq_num = (
SELECT MIN(seq_num)
FROM LST
)
END
END
RETURN @rst