MSSQL 동적 목록 쿼리 (list query sample)

제가 볼려고 그냥 만들어 놓은 것입니다. *_*;;


보시고 오류가 있다면 말씀해 주세요 *_*;;;

더 좋은 쿼리가 있어도 가르쳐 주세요 *_*;;;
제 실력은 여기까지 ㅡ.ㅡ;;;;;

USE [CmnMgt]
GO
/****** Object:  StoredProcedure [dbo].[UP_COMMON_MAIL_SELECT_LIST]    Script Date: 09/06/2010 21:42:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Angeleyes
-- Create date: 2010-09-06
-- Description:	메일 목록을 가져온다.
/*
	UP_COMMON_MAIL_SELECT_LIST 1, 10, 'C.[Create_Date] DESC'
*/
-- =============================================
ALTER PROCEDURE [dbo].[UP_COMMON_MAIL_SELECT_LIST]
	-- Add the parameters for the stored procedure here
	@PAGENUM		INT				= 1
	,@LISTCNT		INT				= 10
	,@ORDER			VARCHAR(50)		= 'C.[Create_Date] DESC'
	,@SEARCH		VARCHAR(500)	= ''
	,@LANG			VARCHAR(10)		= 'ko'
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @TOPINT INT
	DECLARE @TOPEND INT

	SET @TOPINT = (@PAGENUM - 1) * @LISTCNT + 1
	SET @TOPEND = @PAGENUM * @LISTCNT
      
	DECLARE @QUERY_BASE NVARCHAR(4000)
	DECLARE @QUERY_LIST NVARCHAR(4000)
	DECLARE @QUERY_MAXS NVARCHAR(4000)
	
	SET @QUERY_BASE = '
			SELECT
				ROW_NUMBER() OVER(ORDER BY ' + @ORDER + ') AS RNO
				,C.[MailCode]
				,C.[Mail_Type]
			FROM 
				[dbo].[tb_Common_Mail] C
			WHERE
				1 = 1
			' + @SEARCH + ''
				
	SET @QUERY_LIST = '
	WITH #TEMP AS(
		SELECT
			RNO AS RNO
			,[MailCode]
			,[Mail_Type]
		FROM
		(
			'+ @QUERY_BASE +'
		) AS TD
		WHERE
			RNO BETWEEN '+ CONVERT(VARCHAR, @TOPINT) + ' AND '+ CONVERT(VARCHAR, @TOPEND) + '
	)
	
	SELECT 
		TD.[RNO]
		,TD.[MailCode]
		,TD.[Mail_Type]
		,CT.[CATEGORY_NAME]
		,OD.[Mail_Title]
		,OD.[Mail_State]
		,OD.[From_Name]
		,OD.[From_Mail]
		,OD.[AuthorID]
		,[CmnMgt].[dbo].[UF_COMMON_USER_SELECT_USER_NAME](OD.[AuthorID], ''' + @LANG + ''') AS [USER_NAME]
		,OD.[Create_Date]
		,CASE WHEN OD.[Modify_Date] IS NULL THEN OD.[Create_Date] ELSE OD.[Modify_Date] END AS [Modify_Date]
		,CASE WHEN OD.[Create_Date] >= GETDATE() -1 THEN 1
			ELSE 0 END AS [IS_NEW]
	FROM 
		#TEMP AS TD
	INNER JOIN [dbo].[tb_Common_Mail] AS OD WITH(NOLOCK)
	ON	TD.[MailCode] = OD.[MailCode]
	JOIN [CmnMgt].[dbo].[tb_Category] AS CT WITH(NOLOCK)
	ON	OD.[Mail_Type] = CT.[CATEGORY_CODE]'
	
	SET @QUERY_MAXS = '
	SELECT
		COUNT([MailCode]) AS [MAX]
	FROM
	(
		'+ @QUERY_BASE +'
	)T'

--	PRINT (@QUERY_LIST + @QUERY_MAXS)
	EXEC (@QUERY_LIST + @QUERY_MAXS)
END

감사합니다.