這是我寫的一個store procedure 要把資料記錄原來是直的展成橫的方式展現，可是要產生一個tmp table 卻
一直無法順利產生，都會show 出"Invaild object name "#paytmp"......請各位高手耐心看完我的store procedure
CREATE PROCEDURE RptPayDetail_001 @Pay_Ym char(5) AS
Declare @sql_string as varchar(300)
Declare @Sql_Modi as varchar(100)
declare @Rpt_String as varchar(200)
Declare @Field_name as Char(6)
Declare @Emp_id char(6),@Subject_no char(6),@Pay_sum int,@Tax_yn char(1),@Add_yn char(1),@Emp_name char(10)
Select @Sql_string = "Declare PayCursor Scroll Cursor For select distinct subject_no from Pay"+substring(@Pay_ym,1,4) + " Where Up_dn = '"+
substring(@Pay_ym,5,1)+"'"
Exec (@Sql_string)
Select @Sql_string = "Create Table #PayTmp (Emp_id char(6),Emp_name Char(10),"
Select @Rpt_String = "Select Emp_id,Emp_name,"
Open PayCursor
Fetch Next From PayCursor Into @Field_name
While (@@Fetch_status ＜＞ -1)
Begin
Select @Sql_string = @Sql_string + @Field_name + " Int "
Select @Rpt_String = @Rpt_String + @Field_name
Fetch Next From PayCursor Into @Field_name
If @@fetch_status ＜＞ -1
Begin
Select @sql_string = @sql_string +','
Select @Rpt_String = @Rpt_String + ','
End
End
Select @Sql_string = @Sql_string + " ) "
Select @Rpt_String = @Rpt_String + " From #PayTmp"
Exec (@Sql_string)-------------------------------------------------為什麼我都不會產生這一個tmp table 呢？可是我如果
Deallocate PayCursor 把這一行指令另外拿出來執行卻可以產生tmp table 呢？救救我吧？
Select @Sql_string = "Declare PayCursor Scroll Cursor For Select Distinct A.Emp_id,B.Emp_name from Pay"+substring(@Pay_ym,1,4) +
" A,Employee B Where A.Up_dn='"+substring(@Pay_ym,5,1)+ "' and A.emp_id = B.emp_id order by A.Emp_id"
Exec (@Sql_string)
Open PayCursor
Fetch First From PayCursor Into @Emp_id,@Emp_name
While (@@Fetch_status ＜＞ -1)
Begin
Insert Into #PayTmp (Emp_id,Emp_name) Values (@Emp_id,@Emp_name)
Fetch First From PayCursor Into @Emp_id,@Emp_name
End
Deallocate PayCursor
Select @Sql_string = "Declare PayCursor Scroll Cursor For Select Emp_id,Subject_no,Pay_sum,Tax_yn,Add_yn from Pay"+substring(@Pay_ym,1,4) +
" Where Up_dn='"+substring(@Pay_ym,5,1)+ "' order by Emp_id"
Exec (@Sql_string)
Open PayCursor
Fetch First From PayCursor Into @Emp_id,@Subject_no,@Pay_sum,@Tax_yn,@Add_yn
While (@@Fetch_status ＜＞ -1)
Begin
Select @Sql_Modi = "Update into #PayTmp Set " + rtrim(@Subject_no) + "= " + convert(varchar,@Pay_sum)+" Where Emp_id='" + @Emp_id + "' "
Exec (@Sql_Modi)
Fetch Next From PayCursor Into @Emp_id,@Subject_no,@Pay_sum,@Tax_yn,@Add_yn
End
Exec (@Rpt_String)
drop table #payTmp