If statement in stored proc
I have a report to which a parameter (user name) is passed by a user. The report then returns client information relative to the parameter. A user should be able to see clients that relate only to themselves and other users in their branch.
I am wanting to restrict what parameter they can pass to only user names that are in their own branch.
To do this I have created a proc (below) and it is working fine when it is run in QA, but I am getting the below error when I try to run the report:
Reporting Services Error
----------------------------------------------------------------------------
An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Invalid attempt to read when no data is present.
-----------------------------------------------------------------------------
The sp looks like this:
ALTER PROC prc_GetListOfClients
(
@Advisor varchar (10)
)
AS
BEGIN
DECLARE @Caller VARCHAR(10)
DECLARE @site tinyint
DECLARE @BranchCode tinyint
SET @Caller = USER_NAME()
SET @site = (select site from clientAgreements..users where NtUser =
@Caller)
SET @BranchCode = (select site from clientAgreements..users where [user_id] = @Advisor) IF @site = @BranchCode select clcode,BrokerageNZD,cp_sub_class,primary_adviser,c p_name,phone_business,phon
e_private,phone_mobile,address1,address2,address3,
city_district,postal_code
from vCA_Received_Report
where primary_adviser = @Advisor
order by primary_adviser , BrokerageNZD desc ELSE print 'You are not authorised to view this report'
END
If I change the proc to:
ALTER PROC prc_GetListOfClients
(
@Advisor varchar (10)
)
AS
BEGIN
select clcode,BrokerageNZD,cp_sub_class,primary_adviser,c p_name,phone_business,phone_private,phone_mobile,a ddress1,address2,address3,
city_district,postal_code
from vCA_Received_Report
where primary_adviser = @Advisor
END
It works in both QA and RS.
Any ideas?
TIA.
Possible fix (worked for me)
I also saw this message, but with one difference. After I had changed my proc, the report errored with the same "Invalid attempt to read when no data is present. " message - but when run in RS as well - but was fine in QA.
The answer for me was that when you change the parameters for a proc, RS is not too clever at noticing and updating itself with the changes. The result appears to be that the query runs but with in correct parameters, hence, returning no data.
So, I removed the (old) parameters from Dataset and hit the refresh fields button. It was then necessary to adust them in the Report Parameters area of the Layout section.
After I'd done this all was well.
Nick.
