param from function to sp to report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

param from function to sp to report

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2009-06-08 : 15:35:10

I have been working on this for awhile and want to make sure I have the best solution. My data is originally pulled from two table in a View. I then need to pull two weeks worth of payroll by having a @StartDate parameter and also an @WorkLoc parameter. I though this would be best as a function (I could be totally wrong). The function is:

CREATE FUNCTION [dbo].[FCTN_WellService_PayrollTableFunction] (
@Startdate datetime,
@WorkLoc nvarchar (10)
)
RETURNS TABLE
AS RETURN (

WITH payroll
AS(
SELECT
ROW_NUMBER() OVER (PARTITION BY employeeNumber, payweek ORDER BY date) AS TransNumber,
t.PayWeek, t.Date, t.EmployeeNumber, t.EmployeeName, t.JobTitle, t.Hours, t.Hours1, t.CostCenter,
t.PayCategory, t.TimeClockID, t.Status, t.StartDate, t.Entity, t.GLType, t.PayGroup, t.PayGroupCode,
t.LocationGLCode, t.HourlyPayRate AS Rate, t.WorkLocation
FROM VW_WellService_PayrollView2 t
WHERE EmployeeNumber='101671'AND(Date BETWEEN DATEADD(DAY,-14,@StartDate) AND @StartDate) AND WorkLocation=@WorkLoc
)
SELECT
transNumber,payweek,date,employeeNumber,employeename,jobtitle,hours,hours1,ca.totalHours,costcenter,
paycategory,timeclockID,status,startdate,entity,gltype,paygroup,paygroupcode,locationglcode,
rate,worklocation,

CASE
WHEN TotalHours <=40 THEN Hours
WHEN TotalHours >40 AND TotalHours-40 <= Hours THEN (Hours-(TotalHours-40))
ELSE 0 END AS HrsREG,

CASE
WHEN TotalHours >40 AND TotalHours-40 > Hours THEN Hours
WHEN TotalHours >40 AND TotalHours-40 <= Hours THEN (TotalHours-40)
ELSE 0 END AS HrsOT

from Payroll p

cross apply (
select sum(hours) as totalHours
from payroll
where employeeNumber = p.employeeNumber and payweek = p.payweek and transNumber <= p.transNumber
) ca

)

I then have a stored procedure as follows that FAILS. The error when running follows the script. It could be something very simple or could be that I have thought this out all wrong. I would like the parameters from the function also in the Stored Procedure and then have these pass to the report. Where have I failed this?

When looking at this query were I have two values in the FROM statement it gives me what i'm looking for:

WITH FINALPAY AS(
SELECT

EmployeeNumber,TimeClockID,EmployeeName,WorkLocation,CostCenter,Rate,
GLType,locationglcode,Entity,

CASE WHEN PayCategory ='VAC' THEN HrsREG ELSE 0 END AS VAC,
CASE WHEN PayCategory ='HOL' THEN HrsREG ELSE 0 END AS HOL,
CASE WHEN PayCategory ='SHOP' THEN HrsREG ELSE 0 END AS SHOP,
CASE WHEN PayCategory ='SHOP' THEN HrsOT ELSE 0 END AS OTSHP,
CASE WHEN PayCategory ='SHOP1' THEN HrsREG ELSE 0 END AS SHOP1,
CASE WHEN PayCategory ='SHOP1' THEN HrsOT ELSE 0 END AS OTSH1,
CASE WHEN PayCategory ='FLOOR' THEN HrsREG ELSE 0 END AS FLOOR,
CASE WHEN PayCategory ='FLOOR' THEN HrsOT ELSE 0 END AS OTFLOR,
CASE WHEN PayCategory ='DERIC' THEN HrsREG ELSE 0 END AS DERIC,
CASE WHEN PayCategory ='DERIC' THEN HrsOT ELSE 0 END AS OTDRK,
CASE WHEN PayCategory ='OPRTR' THEN HrsREG ELSE 0 END AS OPRTR,
CASE WHEN PayCategory ='OPRTR' THEN HrsOT ELSE 0 END AS OTOPR,
CASE WHEN PayCategory ='SWAB' THEN HrsREG ELSE 0 END AS SWAB,
CASE WHEN PayCategory ='SWAB' THEN HrsOT ELSE 0 END AS OTSWA,
CASE WHEN PayCategory IN ('TRAVL','DRV') THEN HrsREG ELSE 0 END AS DRV,
CASE WHEN PayCategory IN ('TRAVL','DRV') THEN HrsOT ELSE 0 END AS OTDRV,
CASE WHEN PayCategory ='BONJB' THEN Rate ELSE 0 END AS BONJB,
CASE WHEN PayCategory ='SAFTY' THEN Rate ELSE 0 END AS SAFTY,

PayGroup,PayGroupCode

FROM dbo.FCTN_WellService_PayrollTableFunction ('5/8/2009','MWSBOW')AS fctn

)

SELECT
EmployeeNumber,MAX(TimeClockID)TimeClockID,MAX(EmployeeName)EmployeeName,MAX(WorkLocation)WorkLocation,CostCenter,Rate,
MAX(GLType)GLType,MAX(locationglcode)locationglcode,MAX(Entity)Entity,
SUM(VAC)VAC,SUM(HOL)HOL,SUM(SHOP)SHOP,SUM(OTSHP)OTSHP,SUM(SHOP1)SHOP1,SUM(OTSH1)OTSH1,
SUM(FLOOR)FLOOR,SUM(OTFLOR)OTFLOR,SUM(DERIC)DERIC,SUM(OTDRK)OTDRK,SUM(OPRTR)OPRTR,SUM(OTOPR)OTOPR,
SUM(SWAB)SWAB,SUM(OTSWA)OTSWA,SUM(DRV)DRV,SUM(OTDRV)OTDRV,SUM(BONJB)BONJB,SUM(SAFTY)SAFTY,
MAX(PayGroup)PayGroup,MAX(PayGroupCode)PayGroupCode

FROM FINALPAY
GROUP BY EmployeeNumber,CostCenter,Rate

BUT when trying to turn this into a Stored Procedure is where I can't figure it out. Again, i would like to have these two parameters pass to SSRS for end-users to enter in the values. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 14:12:50

whats the purpose of CTE? why cant you directly use function in final select? also whats the error you're getting?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources