none
no parameters and arguments were supplied, does anyone can help?

    Question

  • Does anyone can help me? I need to establish a SSRS 2008 report. However, when I run the "debugging", an error message has been shown "Procedure Stock_Trans has no parameters and arguments were supplied".

    USE [testDB]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description: <Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[Stock_Trans]

    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;

    Declare @start_date datetime
    Declare @end_date datetime
    Declare @item_code [nvarchar](20)
    set @item_code = 'AB6200'
    set @start_date = '01/01/2010 00:00:00.000'
    set @end_date = '07/20/2013 00:00:00.000'

    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
    --and i.document_date >= '01/01/2010 00:00:00.000' 
    --and i.document_date <= '07/20/2013 00:00:00.000'
    ) 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
    --and document_date < '01/01/2010 00:00:00.000'  
    ) 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'
    --19/07/2013 11:46 Join Goodsreceipt_item table
    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 = @item_code

    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 3:09 AM

Answers

  • Hi,

    In SSRS , when use variables inside query/stored procedure in the dataset , like

     ALTER PROCEDURE [dbo].[Stock_Trans]
    @start_date varchar(20), 
    @end_date varchar(20),
    @item_code varchar(20)
    AS
    BEGIN
    ---
    END
    or 
    SELECT * FROM [Stock_Trans] WHERE item_code = @item_code 

     it will be created as parameters in SSRS , in your case you are assigning values for the variables @start_date , @end_date ,@item_code in stored procedure itself , so under parameters delete @start_date , @end_date ,@item_code  or if you want these parameters to be passed as input , after designing report , goto preview pane enter the input parameter value and view the report for the corresponding input .

    Related reference links

    http://msdn.microsoft.com/en-us/library/ms155917%28v=sql.90%29.aspx

    http://ssrstutorials.blogspot.in/2012/07/lesson-7-creating-ssrs-parameterized.html


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.



    Monday, July 22, 2013 3:21 AM
    Moderator

All replies

  • Hi, Do you get error when you run this in SSMS? How is it that you are executing this stored procedure, is it something like below?

    EXEC Stock_Trans
    
    GO

    Ione

    Monday, July 22, 2013 3:17 AM
  • Hi

    I get an error when it runs in SSRS 2008.

    and the executing syntax is EXEC Stock_Trans

    Monday, July 22, 2013 3:20 AM
  • Hi,

    In SSRS , when use variables inside query/stored procedure in the dataset , like

     ALTER PROCEDURE [dbo].[Stock_Trans]
    @start_date varchar(20), 
    @end_date varchar(20),
    @item_code varchar(20)
    AS
    BEGIN
    ---
    END
    or 
    SELECT * FROM [Stock_Trans] WHERE item_code = @item_code 

     it will be created as parameters in SSRS , in your case you are assigning values for the variables @start_date , @end_date ,@item_code in stored procedure itself , so under parameters delete @start_date , @end_date ,@item_code  or if you want these parameters to be passed as input , after designing report , goto preview pane enter the input parameter value and view the report for the corresponding input .

    Related reference links

    http://msdn.microsoft.com/en-us/library/ms155917%28v=sql.90%29.aspx

    http://ssrstutorials.blogspot.in/2012/07/lesson-7-creating-ssrs-parameterized.html


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.



    Monday, July 22, 2013 3:21 AM
    Moderator
  • Hi

    Your sp has three input variables

    Declare @start_date datetime
    Declare @end_date datetime
    Declare @item_code [nvarchar](20)

    while executing this sp from SSRS it is expeting input values

    eg: EXEC [dbo].[Stock_Trans] @start_date  , @end_date  , @item_code

    Assign related report parameters to these at query parameters.


    Prav

    Monday, July 22, 2013 3:23 AM