[Drew Rosen]

I am using a stored procedure in Visual Studio 2015.  The fields show up in SSRS but the report does not show any data.  The following is the stored procedure :

USE [ScribeDat]
GO
/****** Object: StoredProcedure [dbo].[sp_PreviousEmployees] Script Date: 1/30/2022 12:01:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[sp_PreviousEmployees]

@ssno varchar(12),
@Firstname varchar(40),
@Lastname varchar(40),
@bysocial varchar(1)

as
/*
declare @ssno varchar(12),
@firstname varchar(40),
@lastname varchar(40),
@bysocial varchar(1)

set @ssno =’5′
set @firstname=’drew’
set @lastname=’rosen’
set @bysocial=’N’
*/

If OBJECT_ID(N’TEMPDB..#ded401′) IS NOT NULL DROP TABLE #ded401
CREATE TABLE #ded401 (Companyid varchar(5)
,systemid int
,Userid varchar(20)
,amount numeric(18,5))
INSERT INTO #ded401
SELECT
Employees.CompanyID
,Employees.SystemAssignedPersonID
,max(Employees.UserAssignedEmployeeID) as userid
,sum(HistoryCheckBenefitsEmployeeSide.DeductionAmount) as amount
FROM ((SUIT.dbo.HistoryCheckBenefitsEmployeeSide HistoryCheckBenefitsEmployeeSide INNER JOIN
SUIT.dbo.HistoryCheckRecords HistoryCheckRecords ON
(HistoryCheckBenefitsEmployeeSide.PayrollID=HistoryCheckRecords.PayrollID) AND
(HistoryCheckBenefitsEmployeeSide.PayCheckID=HistoryCheckRecords.PayCheckID)) INNER JOIN
SUIT.dbo.Employees Employees ON (HistoryCheckRecords.SystemAssignedPersonID=Employees.SystemAssignedPersonID) AND
(HistoryCheckRecords.CompanyID=Employees.CompanyID))
WHERE HistoryCheckBenefitsEmployeeSide.DeductionAmount<>0 AND
(HistoryCheckBenefitsEmployeeSide.BenefitID=’100′ OR
HistoryCheckBenefitsEmployeeSide.BenefitID=’ROTH’ OR HistoryCheckBenefitsEmployeeSide.BenefitID=’Roth401′)
group by employees.companyid,employees.SystemAssignedPersonID
order by employees.CompanyID,employees.SystemAssignedPersonID

If OBJECT_ID(N’TEMPDB..#statuschange’) IS NOT NULL DROP TABLE #statuschange
CREATE TABLE #statuschange (Companyid varchar(5)
,systemid int
,transactiontypeid varchar(10)
,statuschangereasonid varchar(10)
,effectivedate date)
INSERT INTO #statuschange
select
a.companyid
,a.SystemAssignedPersonID
,a.transactiontypeid
,a.statuschangereasonid
,a.effectivedate
from suit.dbo.employeesemploymentstatuschanges a
order by a.companyid,a.SystemAssignedPersonID,a.effectivedate

select
employees.companyid
,employees.SystemAssignedPersonID
,employees.UserAssignedEmployeeID
,people.firstname
,people.lastname
,people.SocialSecurityNumber
,employees.employeestatusid
,employees.employmenttypeid
,employees.originalhiredate
,employees.currenthiredate
,employees.FulltimeHireDate
,employees.PensionPlanDate
,employees.LastPaidDate
,case
when ded401.amount > 0 then ‘Yes’
else
‘No’
end as k401
,status.transactiontypeid
,status.statuschangereasonid
,status.effectivedate
,masteremptypes.Description as emptype
,masterchangereasons.Description as changereason
from SUIT.dbo.employees employees inner join suit.dbo.people people on
employees.SystemAssignedPersonID = people.SystemAssignedPersonID left outer join
#ded401 ded401 on employees.companyid = ded401.Companyid and employees.SystemAssignedPersonID=ded401.systemid full outer join
#statuschange status on employees.CompanyID = status.companyid and employees.SystemAssignedPersonID = status.systemid inner join
suit.dbo.MasterEmploymentTypes masteremptypes on employees.EmploymentTypeID = masteremptypes.EmploymentTypeID inner join
suit.dbo.masteremploymentstatuschangereasons masterchangereasons on status.statuschangereasonid = masterchangereasons.StatusChangeReasonID
where ((people.socialsecuritynumber = @ssno and @bysocial = ‘Y’) or
(people.firstname=@firstname and people.lastname=@lastname and @bysocial=’N’))
order by employees.companyid,employees.SystemAssignedPersonID,status.effectivedate

When I run the stored procedure by commenting out all the lines up to and including the “as” and using the declare and set statements I get the data I am looking for.  The stored procedure did work in visual studio 2015 but now does not show any data.  thanks Drew Rosen 910-221-3561

——————————
Drew Rosen
Carolina Services of Fayetteville, Inc
fayetteville NC
——————————

[Drew Rosen]

I have fixed the report please ignore the post. Thanks

——————————
Drew Rosen
Carolina Services of Fayetteville, Inc
fayetteville NC
——————————
——————————————-