optional parameters 2 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

optional parameters 2

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

]]>