SQL Server Performance

optional parameters 2

Discussion in 'SQL Server Reporting Services' started by Reddy, Jul 22, 2005.

  1. Reddy New Member

    Hi
    I am using a SP for my report which can pass either 1 parameter or none and retreive the data according to the selection. I can do this in QA but I am unable to do when I pass tht SP in my SQL report where I gave 3 drop down parameters to select.
    I am using the follwing SP with 3 drop down parameters(DeptNo,AcctNumA,AcctNumB) in my report.
    can any one pls help me its urgent.
    ---------------------------------------------------------------------------------


    create PROCEDURE dbo.Reports
    (
    @LogonName varchar(10),
    @Deptnochar(3)=NULL,
    @AcctNumA char(4)=NULL,
    @AcctNumBchar(3)=NULL
    )
    AS
    set nocount off
    select Accounts.DeptNo,
    Accounts.AcctNumA,
    Accounts.AcctNumB,
    Accounts.AppropFund,
    Accounts.AppropApp,
    Accounts.AppropFyr,
    Accounts.AppropLgr ,
    Accounts.Organization ,
    convert(char(14), Accounts.AuthorizeAmt, 1) as 'AuthorizeAmt',Accounts.PurposeCode,
    Accounts.BankAccountNum,
    Accounts.BankName,
    Accounts.BankAddr1,
    Accounts.BankAddr2,
    Accounts.BankCity,
    Accounts.BankState,
    Accounts.BankZip1,
    Accounts.BankZip2,
    Accounts.SourceOfFunds,
    Accounts.BankAccountType,
    Accounts.AccountName,
    Departments.DeptName,
    :confused:fficer,
    o.Bond,
    o.Title
    from accounts
    INNER JOIN
    AppPermissions
    ON
    AppPermissions.LogonName = @LogonName
    INNER JOIN
    ComptrollerOffices
    ON
    ComptrollerOffices.DeptNo = Accounts.DeptNo
    AND ComptrollerOffices.Comptroller = AppPermissions.Comptroller

    INNER JOIN
    Departments
    ON
    Departments.DeptNo = Accounts.DeptNo
    inner join Officers o on o.DeptNo+o.AcctNumA+o.ACctNumB = Accounts.DeptNo+Accounts.AcctNumA+Accounts.AcctNumB

    where departments.deptno LIKE ISNULL(@deptno,departments.deptno)
    and accounts.AcctNumA LIKE ISNULL(@AcctNumA,accounts.AcctNumA)
    --and accounts.AcctNumB LIKE ISNULL(@AcctNumA,accounts.AcctNumB)[WHEN I ADD THIS LINE]

    ----------------------------------------------------------------------------------------

    when I add last line for my code, its not working, I am doing wrong?
    can any one please review this, its a gr8 help.
    THANKS

Share This Page