GO
/****** Object: StoredProcedure [dbo].[create_check_date] Script Date: 05/23/2013 13:41:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[create_check_date]
As
set nocount on
set transaction isolation level read uncommitted
--변수선언
declare @day varchar(10)
declare @day_inTime varchar(20)
declare @day_outTime varchar(20)
declare @sql Nvarchar(2000)
declare @sql2 Nvarchar(2000)
declare @sql3 Nvarchar(2000)
select @day = datename(dw,getdate())
if @day = '월요일'
begin
set @day_inTime = 'in_time_mon'
set @day_outTime = 'out_time_mon'
end
Else if @day = '화요일'
begin
set @day_inTime = 'in_time_tue'
set @day_outTime = 'out_time_tue'
end
Else if @day = '수요일'
begin
set @day_inTime = 'in_time_wed'
set @day_outTime = 'out_time_wed'
end
Else if @day = '목요일'
begin
set @day_inTime = 'in_time_thu'
set @day_outTime = 'out_time_thu'
end
Else if @day = '금요일'
begin
set @day_inTime = 'in_time_fri'
set @day_outTime = 'out_time_fri'
end
set @sql = N' insert into SHA_check(UserID, AcaID, check_date,in_time, out_time , check_flag ) '
+ 'select a.UserID,a.AcaID,CONVERT(varchar(10), GETDATE(), 23)as check_date ,b.inTime, b.outTime , ''N'' '
+ ' from('
+ ' select id, UserID, AcaID from sha_student where people=''재원'' and flag=''Y'' '
+ ' )a '
+ ' inner join '
+ ' ( '
+ ' select * from ( select StuID, AcaID, '+@day_inTime+' as ''inTime'','+@day_outTime+' as ''outTime'', '
+ ' reg_date , rank()over(partition by stuid order by reg_date desc ) as ran from SHA_student_weektime )n '
+ ' where ran = 1 '
+ ' )b '
+ ' on a.ID = b.StuID '
+ ' where a.userid not in(select userid from sha_check c where check_date = convert(varchar(10),getdate(),23) and a.userid = c.userid and a.acaid = c.AcaID ) '
--print @sql
execute sp_executesql @sql
-- 임시테이블 삭제
set @sql2 = ' truncate table sha_check_temp '
execute sp_executesql @sql2
-- 임시테이블에 오늘 데이터 삽입
set @sql3 = N' insert into SHA_check_temp(UserID, AcaID, check_date,in_time, out_time , check_flag ) '
+ 'select a.UserID,a.AcaID,CONVERT(varchar(10), GETDATE(), 23)as check_date ,b.inTime, b.outTime , ''N'' '
+ ' from('
+ ' select id, UserID, AcaID from sha_student where people=''재원'' and flag=''Y'' '
+ ' )a '
+ ' inner join '
+ ' ( '
+ ' select * from ( select StuID, AcaID, '+@day_inTime+' as ''inTime'','+@day_outTime+' as ''outTime'', '
+ ' reg_date , rank()over(partition by stuid order by reg_date desc ) as ran from SHA_student_weektime )n '
+ ' where ran = 1 '
+ ' )b '
+ ' on a.ID = b.StuID '
+ ' where a.userid not in(select userid from sha_check c where check_date = convert(varchar(10),getdate(),23) and a.userid = c.userid and a.acaid = c.AcaID ) '
--print @sql
execute sp_executesql @sql3
set nocount off
질문의 요지는 요 한 프로시저에 하나의 execute sp_executesql 밖에 못쓰나요??
@sql 만 실행이되고
나머지 @sql2 , @sql3 은 실행이 안되네요 ㅜㅜ 조언좀 해주시면 감사하겠습니다. ㅜ
Comment 1
-
alima
2013.05.23 14:56
죄송합니다 잘되네요 ㅜ 쿼리문에 오류가 ㅜㅜ