Hi I have used temp table in a procedure and inserting row into temp table ( insert into #temptable select ..... ) in a sequence of process. I noticed the weird behaviour that the Procedure didn't returned any result-set and keep running when I executed and looks hang. But when I try to execute the select query used as a part of insert statement separately, It returned a result-set. what could be the reason why the insert into temptable is not working inside the procedure.

TIA vhm


Edit (MC) Here is the procedure (copied and formatted from comments below):

create PROCEDURE "DBA"."r_sd_stk"
        @as_compcode     char(2),
        @as_stcatcode    char(5),
        @as_edcatcode    char(5),
        @as_stitemno     char(50),
        @as_editemno     char(50),
        @as_stdate       char(10),
        @as_eddate       char(10),
        @as_location     char(10),
        @an_qty_dec      numeric,
        @an_cost_dec     numeric,
        @as_qty_format   char(20),
        @as_cost_format  char(20),
        @as_total_format char(20),
        @ai_stpt         int,
        @ai_stln         int

as
declare  
@ls_12Date      char(10),
@ls_6Date       char(10),
@ls_3Date       char(10),
@ls_4Date       char(10),
@ls_1Date       char(10),
@ld_total       decimal(18,6),
@ls_3SODate     char(10),
@ls_4SODate     char(10),
@ls_1SODate     char(10),
@ls_6SODate     char(10),
@ls_12SODate    char(10),
@ls_RmSODate    char(10)

BEGIN

   create table #stockinvoiced (
      item_no      char(50)      NULL,
      cat_code     char(5)       NULL,
      uom          char(5)       NULL,
      item_descr   char(200)     NULL,
      cat_descr    char(50)      NULL,
      doc_no       char(30)      NULL,
      doc_date     date          NULL,
      qty_12       decimal(18,6) NULL,
      qty_3        decimal(18,6) NULL,
      qty_1        decimal(18,6) NULL,
      warehouse    char(50)      NULL,
      sub_cat      char(5)       NULL,
      subcat_descr char(20)      NULL )

select @ls_12Date = convert(char(10),dateadd(day,1,dateadd(month,-12,@as_eddate)),111)
select @ls_3Date = convert(char(10),dateadd(day,1,dateadd(month,-3,@as_eddate)),111) 
select @ls_4Date = convert(char(10),dateadd(day,1,dateadd(month,-4,@as_eddate)),111) 
select @ls_1Date = convert(char(10),dateadd(day,1,dateadd(month,-1,@as_eddate)),111)
select @ls_6Date = convert(char(10),dateadd(day,1,dateadd(month,-6,@as_eddate)),111)

select @ls_4SODate = convert(char(10),dateadd(day,1,dateadd(month,-4,@as_eddate)),111)
select @ls_3SODate = convert(char(10),dateadd(day,1,dateadd(month,-3,@as_eddate)),111)
select @ls_1SODate = convert(char(10),dateadd(day,1,dateadd(month,-1,@as_eddate)),111)
select @ls_6SODate = convert(char(10),dateadd(day,1,dateadd(month,-6,@as_eddate)),111)
select @ls_12SODate = convert(char(10),dateadd(day,1,dateadd(month,-12,@as_eddate)),111)
select @ls_RmSODate = convert(char(10),dateadd(month,-12,@as_eddate),111)

insert into #stockinvoiced
select D.item_no, A.cat_code, A.uom, A.item_descr, C.descr, M.doc_no, 
       convert(date,M.doc_date), 
       D.qty_order, 0,0, W.name, S.sub_cat, S.descr
from ar_invmaster M, ar_invdtls D, in_item A, in_category C, in_warehouse W, in_sub_cat S
where M.doc_no = D.doc_no
      and M.comp_code = D.comp_code
      and D.comp_code = A.comp_code
      and D.item_id = A.item_id
      and A.comp_code = C.comp_code
      and A.cat_code = C.cat_code
      and C.comp_code = S.comp_code
      and substring(C.cat_code,@ai_stpt,@ai_stln) = S.sub_cat
      and S.cat_pos = @ai_stpt
      and S.cat_len = @ai_stln
      and M.comp_code = W.comp_code
      and M.wh_id = W.wh_id
      and M.inv_status <> 'C'
      and convert(char(10),M.doc_date,111) between @ls_12Date and @as_eddate
      and D.qty_order <> 0
      and D.item_no between @as_stitemno and @as_editemno
      and S.sub_cat between @as_stcatcode and @as_edcatcode
      and W.wh_code like @as_location
      and A.comp_code = @as_compcode

insert into #stockinvoiced
select D.item_no, A.cat_code, A.uom, A.item_descr, C.descr, M.doc_no, 
       convert(date,M.doc_date), 
       0, D.qty_order, 0, W.name, S.sub_cat, S.descr
from ar_invmaster M, ar_invdtls D, in_item A, in_category C, in_warehouse W, in_sub_cat S
where M.doc_no = D.doc_no
      and M.comp_code = D.comp_code
      and D.comp_code = A.comp_code
      and D.item_id = A.item_id
      and A.comp_code = C.comp_code
      and A.cat_code = C.cat_code
      and C.comp_code = S.comp_code
      and substring(C.cat_code,@ai_stpt,@ai_stln) = S.sub_cat
      and S.cat_pos = @ai_stpt
      and S.cat_len = @ai_stln
      and M.comp_code = W.comp_code
      and M.wh_id = W.wh_id
      and M.inv_status <> 'C'
      and convert(char(10),M.doc_date,111) between @ls_3Date and @as_eddate
      and D.qty_order <> 0
      and D.item_no between @as_stitemno and @as_editemno
      and S.sub_cat between @as_stcatcode and @as_edcatcode
      and W.wh_code like @as_location
      and A.comp_code = @as_compcode

insert into #stockinvoiced
select D.item_no, A.cat_code, A.uom, A.item_descr, C.descr, M.doc_no, 
       convert(date,M.doc_date), 
       0, 0, D.qty_order, W.name, S.sub_cat, S.descr
from ar_invmaster M, ar_invdtls D, in_item A, in_category C, in_warehouse W, in_sub_cat S
where M.doc_no = D.doc_no
      and M.comp_code = D.comp_code
      and D.comp_code = A.comp_code
      and D.item_id = A.item_id
      and A.comp_code = C.comp_code
      and A.cat_code = C.cat_code
      and C.comp_code = S.comp_code
      and substring(C.cat_code,@ai_stpt,@ai_stln) = S.sub_cat
      and S.cat_pos = @ai_stpt
      and S.cat_len = @ai_stln
      and M.comp_code = W.comp_code
      and M.wh_id = W.wh_id
      and M.inv_status <> 'C'
      and convert(char(10),M.doc_date,111) between @ls_1Date and @as_eddate
      and D.qty_order <> 0
      and D.item_no between @as_stitemno and @as_editemno
      and S.sub_cat between @as_stcatcode and @as_edcatcode
      and W.wh_code like @as_location
      and A.comp_code = @as_compcode

Select * from #stockinvoiced
END

asked 26 Sep '13, 06:01

vhm's gravatar image

vhm
210131320
accept rate: 0%

edited 27 Sep '13, 09:20

Mark%20Culp's gravatar image

Mark Culp
22.6k9129265

Well, I'd try with "sa_conn_info" to find out whether the connection running the stored procedure is blocked by another connection...

(26 Sep '13, 06:07) Volker Barth

can you post the procedure code?

(26 Sep '13, 06:08) Justin Willey

thanks for the quick response Volker..

just tried with "sa_conn_info" , No blocked connection

(26 Sep '13, 06:16) vhm
Comment Text Removed

PLEASE SHOW US THE CODE!!!

Nobody can solve bugs if they can't see the code.

(26 Sep '13, 07:38) Breck Carter
Comment Text Removed
Comment Text Removed
1

Just a point about practice...It's better to put the code in the question. If you indent all lines with 4 spaces, it gets formatted nicely by the forum software and is easier to read.

(27 Sep '13, 08:19) TonyV

Fully agreed - particularly as we have to guess whether these two comments form one single block of code (the answer seems to be "yes")...

FWIW, instead of indenting, you can also put the whole code within a

<pre>...</pre> tag

or just mark all code lines and use the "code" button (which works only for questions and answers, not for comments, obviously).

(27 Sep '13, 09:02) Volker Barth

What do you mean "hang"? Is there an error message? What does sa_conn_info() show?

(27 Sep '13, 09:56) Breck Carter

it keeps running and no result set returned for long time.

(27 Sep '13, 22:45) vhm
Comment Text Removed

it keeps running and no result set returned for long time.

No error Message

sa_conn_info shows no locking

(30 Sep '13, 03:50) vhm

I have tried using cursor instead of insert into ....select, it works.

any idea why the insert into ...select not working? very weird...

(30 Sep '13, 04:39) vhm
More comments hidden
showing 4 of 10 show all flat view

What I will do first check my result set. Check that the data is clean and the data types are correct. it had happen to me that one column is not in the right sequence.

permanent link

answered 04 Oct '13, 07:01

ClarenceA's gravatar image

ClarenceA
46114
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×19

question asked: 26 Sep '13, 06:01

question was seen: 1,712 times

last updated: 04 Oct '13, 07:01