Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Unanswered: Table Parameter in Stored Procedure

    I am Creating a Procedure in SQL2000 but founding an error, Please help to solve the problem.

    Procedure:
    CREATE PROCEDURE Itmopbalance
    @myfitemcode int,
    @ttype char(1),
    @myfyear char(9),
    @mydatefrom2 datetime,
    @MyDatefrombef datetime,
    @MyDatefrom datetime,
    @MyDateto datetime,
    @mycmp int,
    @mytable varchar(20)
    AS
    IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tempitemstocksumbefore' AND type = 'U')
    DROP table tempitemstocksumbefore
    declare @mytType varchar(9)

    if @ttype ='B'
    begin
    set @myttype = 'B,R'
    set @mytable = 'itemrecqty'
    end

    if @ttype ='P'
    begin
    set @myttype = 'B,P'
    set @mytable = 'itemrecqtyPHY'
    end

    select * into tempitemstocksumbefore from
    (
    select @myfitemcode,sum(itopbal)itopbal,sum(dctrecqty) dctrecqty,sum(siv_mrnqtyiss_ret)siv_mrnqtyiss_ret, sum(recqty)Stockrecqty,sum(issqty)stockissqty,
    sum(siv_mrnqtyiss_ret2)sivissue,sum(issqty2)Dlvchi ssue, (sum(itopbal)+sum(dctrecqty)+sum(siv_mrnqtyiss_ret )+sum(recqty)-sum(issqty)-sum(siv_mrnqtyiss_ret2)-sum(issqty2)) closingbal
    from
    (
    select ititemcode , ISNULL(itopbal,0) itopbal ,0.000 as dctrecqty ,0.000 as SIV_MRNQtyiss_ret ,0.000 as recqty,0.0 issqty ,0.000 as SIV_MRNQtyiss_ret2 ,0.000 as issqty2 from itemopbal where ittype = @ttype and itspyear=@myfyear
    Union
    select dctitemcode ,0 as itopbal,sum(isnull(recqty,0)) dctrecqty,0.0,0.0,0.0,0.0,0.0 from @mytable where DCRECDT>= @mydatefrom2 AND DCRECDT < @MyDatefrombef and dctinclude in @myttype and dccmpcode=@mycmp group by dctitemcode
    Union
    select SIV_MRNItemcode,0.0,0.0,sum(isnull(SIV_MRNQtyiss_r et,0)) SIV_MRNQtyiss_ret,0.0,0.0,0.0,0.0 from siv_mrn where SIV_MRNdate>= @mydatefrom2 and SIV_MRNdate < @MyDatefrombef and SIV_MRNshow in (@mytType) and SIV_MRNType='M' group by SIV_MRNItemcode
    Union
    select Itemcode,0.0,0.0,0.0,sum(isnull(recqty,0)) recqty ,0.0,0.0,0.0 from stocktransfer where chdt>= @mydatefrom2 and chdt < @MyDatefrombef and include in (@mytType) group by itemcode
    Union
    select Itemcode,0.0,0.0,0.0,0.0,sum(isnull(issqty,0)) issqty ,0.0,0.0 from stocktransfer where chdt>= @mydatefrom2 and chdt < @MyDatefrombef and include in (@mytType) group by itemcode
    Union
    select SIV_MRNItemcode,0.0,0.0,0.0,0.0,0.0 ,sum(isnull(SIV_MRNQtyiss_ret,0)) SIV_MRNQtyiss_ret2 ,0.0 from siv_mrn where SIV_MRNdate>= @mydatefrom2 and SIV_MRNdate < @MyDatefrombef and SIV_MRNshow in (@mytType) and SIV_MRNType='S' group by SIV_MRNItemcode
    Union
    select ISSitemcode,0.0,0.0,0.0,0.0,0.0,0.0,sum(isnull(iss qty,0)) issqty2 From issdelvch where ISSchdt>= @mydatefrom2 and ISSchdt < @MyDatefrombef and ISSshow in (@mytType) group by ISSitemcode
    )
    x , item b where x.ititemcode=b.ititemcode and x.itiemcode =@myfitemcode
    ) y

    GO

    Error
    Server: Msg 137, Level 15, State 2, Procedure Itmopbalance, Line 36
    Must declare the variable '@mytable'.
    Server: Msg 170, Level 15, State 1, Procedure Itmopbalance, Line 47
    Line 47: Incorrect syntax near ')'.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Code:
    CREATE PROCEDURE Itmopbalance
    @myfitemcode int,
    @ttype char(1),
    @myfyear char(9),
    @mydatefrom2 datetime,
    @MyDatefrombef datetime,
    @MyDatefrom datetime,
    @MyDateto datetime,
    @mycmp int,
    @mytable varchar(20)
    AS
    IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tempitemstocksumbefore' AND type = 'U')
      DROP table tempitemstocksumbefore
    
    declare @mytType varchar(9)
    
    if @ttype ='B'
      begin
    	set @myttype = 'B,R'
    	set @mytable = 'itemrecqty'
      end
    
    if @ttype ='P'
      begin
    	set @myttype = 'B,P'
    	set @mytable = 'itemrecqtyPHY'
      end
    
    select * into tempitemstocksumbefore 
    from (select @myfitemcode, sum(itopbal) itopbal, sum(dctrecqty) dctrecqty, sum(siv_mrnqtyiss_ret) siv_mrnqtyiss_ret, 
    	sum(recqty) Stockrecqty, sum(issqty) stockissqty, sum(siv_mrnqtyiss_ret2) sivissue, sum(issqty2) Dlvchissue, 
    	(sum(itopbal)+sum(dctrecqty)+sum(siv_mrnqtyiss_ret )+sum(recqty)-sum(issqty)-sum(siv_mrnqtyiss_ret2)-sum(issqty2)) closingbal
          from (
    	select ititemcode , ISNULL(itopbal,0) itopbal ,0.000 as dctrecqty ,0.000 as SIV_MRNQtyiss_ret ,0.000 as recqty,0.0 issqty ,0.000 as SIV_MRNQtyiss_ret2 ,0.000 as issqty2 
    	from itemopbal 
    	where ittype = @ttype and itspyear=@myfyear
    	Union
    	select dctitemcode ,0 as itopbal,sum(isnull(recqty,0)) dctrecqty,0.0,0.0,0.0,0.0,0.0 
    	from @mytable 
    	where DCRECDT>= @mydatefrom2 
    	  AND DCRECDT < @MyDatefrombef 
    	  and dctinclude in @myttype 
    	  and dccmpcode=@mycmp 
    	group by dctitemcode
    	Union
    	select SIV_MRNItemcode,0.0,0.0,sum(isnull(SIV_MRNQtyiss_r et,0)) SIV_MRNQtyiss_ret,0.0,0.0,0.0,0.0 
    	from siv_mrn 
    	where SIV_MRNdate>= @mydatefrom2 
    	  and SIV_MRNdate < @MyDatefrombef 
    	  and SIV_MRNshow in (@mytType) 
    	  and SIV_MRNType='M' 
    	group by SIV_MRNItemcode
    	Union
    	select Itemcode,0.0,0.0,0.0,sum(isnull(recqty,0)) recqty ,0.0,0.0,0.0 
    	from stocktransfer 
    	where chdt>= @mydatefrom2 
    	  and chdt < @MyDatefrombef 
    	  and include in (@mytType) 
    	group by itemcode
    	Union
    	select Itemcode,0.0,0.0,0.0,0.0,sum(isnull(issqty,0)) issqty ,0.0,0.0 
    	from stocktransfer 
    	where chdt>= @mydatefrom2 
    	  and chdt < @MyDatefrombef 
    	  and include in (@mytType) 
    	group by itemcode
    	Union
    	select SIV_MRNItemcode,0.0,0.0,0.0,0.0,0.0 ,sum(isnull(SIV_MRNQtyiss_ret,0)) SIV_MRNQtyiss_ret2 ,0.0 
    	from siv_mrn 
    	where SIV_MRNdate>= @mydatefrom2 
    	  and SIV_MRNdate < @MyDatefrombef 
    	  and SIV_MRNshow in (@mytType) 
    	  and SIV_MRNType='S' 
    	group by SIV_MRNItemcode
    	Union
    	select ISSitemcode,0.0,0.0,0.0,0.0,0.0,0.0,sum(isnull(iss qty,0)) issqty2 
    	From issdelvch 
    	where ISSchdt>= @mydatefrom2 
    	  and ISSchdt < @MyDatefrombef 
    	  and ISSshow in (@mytType) 
    	group by ISSitemcode
    	) x , item b 
       where x.ititemcode=b.ititemcode 
         and x.itiemcode =@myfitemcode) y
    
    GO
    Looks like you missed a couple parentheses in one of the unioned queries (and dctinclude in @myttype ). I am not sure why you are getting the undeclared variable message.

  3. #3
    Join Date
    Jun 2012
    Posts
    2
    Thanks MCrowley, but still error there as:
    Must declare the variable '@mytable'.

    It seems that I am calling a table in parameter which is not correct syntax.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You have declared a VARCHAR(20) parameter as @myTable. You have used @myTable as a table variable.

    Stored procedure parameters and declared variables all work from on "name pool", so even if you DECLARE a table typed @myTable it still won't work. You'll just move where the error is detected.

    DECLARE a table variable with a new name, then you ought to be fine.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •