ms-sql 동적으로 쿼리만들기

ALTER PROCEDURE [dbo].[sp_get_Reff_Info]
@FormCode varchar(100),
@process_id varchar(10),
@nPageSize INT,
@nCurrPage INT,
@title varchar(1000),
@ref_code varchar(100),
@dtStartDate datetime,
@dtEndDate datetime
AS
BEGIN

SET NOCOUNT ON;
DECLARE @strCount NVARCHAR(20)
DECLARE @@strSqlString NVARCHAR(4000)
DECLARE @@strTotalCount NVARCHAR(4000)

BEGIN
SET @strCount = CAST(@nPageSize * @nCurrPage AS NVARCHAR(20))
END
if(@FormCode = 'PROMOTIONAL')
BEGIN
SET @@strSqlString = ' SELECT TOP '+@strCount+' ''' + '' + ''' AS state, a.idn, a.ref_code '
+ ' ,a.title,convert(char, a.initiate_datetime, 120) as initiate_datetime,a.initiator_name '
+ ' ,(select User_name from ADM_USER where user_id = a.last_user_id) as last_user_name '
+ ' ,a.initiator_dept,originalxml '
+ ' from doc a '
+ ' where process_id in ( ''1'',''2'',''3'',''4'' ) and is_finished =''Y'' '
+ ' and convert(char, a.initiate_datetime, 120) >= convert(char, @dtStartDate, 120) '
+ ' and convert(char, a.initiate_datetime, 120) < DateAdd(day, 1, @dtEndDate) '
+ CASE
WHEN (@title = '')
THEN
''
ELSE
' and a.title like ''%'+@title+'%'' '
END
+ CASE
WHEN (@ref_code = '')
THEN
''
ELSE
' and a.ref_code like ''%'+@ref_code+'%'' '
END
+ ' order by ref_code '
SET @@strTotalCount = ' SELECT count(*) as TOTALCOUNT '
+ 'from doc a where process_id in ( ''1'',''2'',''3'',''4'' ) and is_finished =''Y'' '
+ ' and convert(char, a.initiate_datetime, 120) >= convert(char, @dtStartDate, 120) '
+ ' and convert(char, a.initiate_datetime, 120) < DateAdd(day, 1, @dtEndDate) '
+ CASE
WHEN (@title = '')
THEN
''
ELSE
' and (a.title = '''+@title+''') '
END
+ CASE
WHEN (@ref_code = '')
THEN
''
ELSE
' and a.ref_code like ''%'+@ref_code+'%'' '
END

END
else
BEGIN
SET @@strSqlString =' SELECT TOP '+@strCount+' ''' + '' + ''' AS state, a.idn, a.ref_code '
+ ' ,a.title,convert(char, a.initiate_datetime, 120) as initiate_datetime,a.initiator_name '
+ ' ,(select User_name from ADM_USER where user_id = a.last_user_id) as last_user_name '
+ ' ,a.initiator_dept,originalxml '
+ ' from doc a '
+ ' where process_id = ''6'' and is_finished =''Y'' '
+ ' and convert(char, a.initiate_datetime, 120) >= convert(char, @dtStartDate, 120) '
+ ' and convert(char, a.initiate_datetime, 120) < DateAdd(day, 1, @dtEndDate) '
+ CASE
WHEN (@title = '')
THEN
''
ELSE
' and a.title like ''%'+@title+'%'' '
END
+ CASE
WHEN (@ref_code = '')
THEN
''
ELSE
' and a.ref_code like ''%'+@ref_code+'%'' '
END
+ ' order by ref_code '
SET @@strTotalCount = ' SELECT count(*) as TOTALCOUNT '
+ 'from doc a where process_id = ''6'' and is_finished =''Y'' '
+ ' and convert(char, a.initiate_datetime, 120) >= convert(char, @dtStartDate, 120) '
+ ' and convert(char, a.initiate_datetime, 120) < DateAdd(day, 1, @dtEndDate) '
+ CASE
WHEN (@title = '')
THEN
''
ELSE
' and (a.title = '''+@title+''') '
END
+ CASE
WHEN (@ref_code = '')
THEN
''
ELSE
' and a.ref_code like ''%'+@ref_code+'%'' '
END
END

EXEC SP_EXECUTESQL @@strSqlString, N'@dtStartDate datetime, @dtEndDate datetime', @dtStartDate, @dtEndDate
EXEC SP_EXECUTESQL @@strTotalCount, N'@dtStartDate datetime, @dtEndDate datetime', @dtStartDate, @dtEndDate
END