none
Does anyone can help? "Procedure or function Trans_List has too many arguments specified"

    Question

  • Does anyone can help?

    I m trying to pass multiple values to @item_code (e.g. 'AB1002' and 'AB1003'), but the SSRS 2008 promoted me an error "Procedure or function Trans_List has too many arguments specified" when start debugging this report.

    I have already defined the @item_code parameter as "allow multiple values" and "allow blank values" in ssrs 2008.

    Please help on this. Thanks so much!

    USE [testDB]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[Trans_List]

    @item_code [nvarchar](20),
    @start_date datetime,
    @end_date datetime
    AS
    BEGIN

    CREATE TABLE #table_SKTrans (
    item_code [nvarchar](20),
    document_date datetime,
    document_no [nvarchar](200),
    [fob_cost] numeric(20,6),
    [fcc_freight_cost]  numeric(20,6),
    [total_volume_cbm] numeric(20,6),
    Landed_Cost numeric(20,6),
    Stock_in numeric(20,6),
    Stock_in_AMT numeric(20,6),
    Stock_out numeric(20,6),
    Stock_out_AMT numeric(20,6)
    )

    SET NOCOUNT ON;

    insert into #table_SKTrans
    select * 
    from  ( --aaa
    select 
    aaa.item_code
    , aaa.document_date
    , aaa.document_no
    , [fob_cost]
    , [fcc_freight_cost]
    , [total_volume_cbm]
    , ([fob_cost]+[fcc_freight_cost]+[total_volume_cbm]) AS 'Landed_Cost'
    , aaa.qty_in AS 'Stock_in'
    , Convert(numeric(20,2),([fob_cost]+[fcc_freight_cost]+[total_volume_cbm]) * qty_in) AS 'Stock_in_AMT'
    , aaa.qty_out As 'Stock_out'
    , Convert(numeric(20,2),([fob_cost]+[fcc_freight_cost]+[total_volume_cbm]) * qty_out) AS 'Stock_out_AMT'
    from ( --allst
    select allst.*, doctb.document_name, 0 as unit_cost, 0 as amount
    from ( 
    select i.document_type
    , i.document_no
    , i.document_date
    , i.item_code
    , i.warehouse_code  
    , case when i.qty_on_hand > 0 then convert(int, i.qty_on_hand) 
    else 0 end as qty_in 
    , case when i.qty_on_hand < 0 then convert(int, -i.qty_on_hand) 
    else 0 end as qty_out 
    , convert(int, i.qty_on_hand) as qty_balance
    , im.base_unit as uom  
    from inventory_log i 
    left join item_master im 
    on i.company_code = im.company_code 
    and i.item_code = im.item_code  
    where i.qty_on_hand <> 0 
    and i.company_code = 'COM-001'  
    and i.document_date >= @start_date  
    and i.document_date <= @end_date
    ) allst
    left join ( --doctb
    select document_type, document_no, document_name 
    from ( --docname
    select 'INV' as document_type, invoice_no as document_no, customer_name_eng as document_name 
    from invoice_master  
    where cancelled = 0 
    and company_code = 'COM-001'  
    union --all 
    select 'GR' as document_type, goodsreceipt_no as document_no, vendor_name_eng as document_name 
    from goodsreceipt_master 
    where cancelled = 0 
    and company_code = 'COM-001'
    union --all  
    select 'STIN' as document_type, stin_no as document_no, main_name as document_name 
    from stin_master  
    where cancelled = 0 
    and company_code = 'COM-001' 
    union --all  
    select 'STOUT' as document_type, stout_no as document_no, main_name as document_name 
    from stout_master  
    where cancelled = 0 
    and company_code = 'COM-001' 
    ) docname  
    )doctb on allst.document_type = doctb.document_type  
    and allst.document_no = doctb.document_no 
    ) aaa 
    left join ( --opamt
    select openamt.item_code
    , openamt.warehouse_code
    , sum(qty_balance) as op_balance
    , 0 as op_amount

    from ( --openamt
    select item_code
    , warehouse_code
    , convert(int, qty_on_hand) as qty_balance
    , 0 as amount 

    from inventory_log  
    where qty_on_hand <> 0 
    and company_code = 'COM-001'  
    and document_date < @start_date
    ) openamt  group by openamt.item_code, openamt.warehouse_code 
    ) opamt on aaa.item_code = opamt.item_code 
    and aaa.warehouse_code = opamt.warehouse_code 
    left join item_master --bbb
    on aaa.item_code = item_master.item_code 
    and item_master.company_code = 'COM-001'

    left join [goodsreceipt_item]
    on aaa.item_code = [goodsreceipt_item].item_code
    and [goodsreceipt_item].company_code = 'COM-001'
    Group by  aaa.item_code
    , aaa.document_date
    , aaa.document_no
    , fob_cost
    , qty_balance
    , op_balance
    , qty_in
    , qty_out
    , [fcc_freight_cost]
    , [total_volume_cbm]

    ) bbb where  1=1   
    and @item_code IS NULL OR upper(item_code) IN (@item_code)
    order by  item_code, document_date, document_no

    SELECT 
    item_code ,
    document_date ,
    document_no ,
    [fob_cost] ,
    [fcc_freight_cost]  ,
    [total_volume_cbm] ,
    Landed_Cost ,
    Stock_in ,
    Stock_in_AMT ,
    Stock_out ,
    Stock_out_AMT
    FROM #table_SKTrans
    ORDER by item_code, document_date, document_no

    Drop table #table_SKTrans

    END

     
    Monday, July 22, 2013 4:03 AM

Answers

All replies

  • Hi, You need to use a split function to pass multi values from SSRS report.

    CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
    RETURNS table
    AS
    RETURN (
        WITH Pieces(pn, start, stop) AS (
          SELECT 1, 1, CHARINDEX(@sep, @s)
          UNION ALL
          SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
          FROM Pieces
          WHERE stop > 0
        )
        SELECT pn,
          SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
        FROM Pieces
      )
    
    GO

    and change your @item_code to SELECT * FROM dbo.Split(@item_code, ',')


    Ione


    • Edited by ione721 Monday, July 22, 2013 4:13 AM typo
    Monday, July 22, 2013 4:12 AM
  • hi,

    does it like that? (Split function has been created already in my db)

    "and @item_code IS NULL OR upper(item_code) IN (SELECT * FROM dbo.Split(@item_code,','))"

    but I got an error when executed my sp...

    Monday, July 22, 2013 4:28 AM
  • Msg 116, Level 16, State 1, Procedure Stock_Trans, Line 178
    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

    an error message here...

    Monday, July 22, 2013 4:32 AM
  • When you already have a split function do not create it. Just use whatever function you have for "Item Code". BTW what is the error that you get.


    Ione

    Monday, July 22, 2013 4:38 AM
  • hi Ione

    I got an error from sql server when I modified my sp...

    "and @item_code IS NULL OR upper(item_code) IN (SELECT * FROM dbo.Split(@item_code,','))"

    error message:

    Msg 116, Level 16, State 1, Procedure Stock_Trans, Line 178
    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

    thanks!

    Monday, July 22, 2013 4:41 AM
  • Hi umanuwin,

    Instead of "SELECT * FROM dbo.Split(@item_code,','))" , you need to give the exact column name

    for example:

    SELECT <<yourcolumnname>> FROM dbo.Split(@item_code,','))"
     

    your select * is returning more than one column and IN Clause works on single column.

    To get the columns name you need to execute something like his or view the metadata of function.

    SELECT * FROM dbo.Split('hello,world',','))"


    Regards Harsh

    Monday, July 22, 2013 4:51 AM
  • Thanks Everyone! I got it!

    However, @item_code is a dynamic values. The values is depending on what values has been input by user.

    And I tried to test in SSRS 2008... I found the all of the item codes from DB have been shown at the result even I specify the item code (e.g. AB1002)... 

    So, does any setting problem for my parameter in ssrs 2008?

    Please help! Thanks so much!

    Monday, July 22, 2013 5:15 AM
  • Hi umanuwin,

    You need to change is condition, I believe this is evaluating true always

    need to add brackets around OR Condition:

    where  1=1   
    and ( 
          @item_code IS NULL OR upper(item_code) IN (SELECT * FROM dbo.Split(@item_code,','))
        )


    Regards Harsh

    Monday, July 22, 2013 5:21 AM
  • Hi Harsh

    Thanks for your help again!

    I tried to modify my code in sp as you mentioned. However, still all of the item codes have been shown at the result even I specified the item_code (e.g AB1002)

    Please help! Thanks!

    Monday, July 22, 2013 5:30 AM
  • Hi Umanuwin,

    Base on your description, this issue can be related to the definition of the stored procedure, you can debug the T-SQL query to check which value is no correct. For the detail information about how to debug T-SQL query, please see:
    How to debug your SQL server query

    If the issue persists, since I am not an expert on T-SQL, I’m afraid you might need get help in this forum:
    http://social.technet.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, July 23, 2013 6:57 AM
  • I see no EXISTS clause in your sp...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, July 23, 2013 7:50 AM