CODEWITHSUNDEEP
sql-server
theLuckyOne
theLuckyOne

Reputation: 272

pass a loop as parameter to a stored procedure

This is my stored procedure. It takes INT as input which is essentially an staff id.

CREATE PROCEDURE [dbo].[spMonthlyPayroll]
@StaffId int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT        StaffId, Name, CNICNumber, Salary, BankAccountNumber,[dbo].[totalWorkingDaysScalar](StaffId, datename(month, GETDATE())) as TotalDaysWorked, [dbo].[totalDaysWorkedScalar](StaffId, datename(month, GETDATE())) as TotalDaysWorked,
    (CONVERT([date],getdate())) AS PayrollDate,
    (CONVERT([date],dateadd(month,(-1),dateadd(month,datediff(month,(0),getdate())+(1),(0))))) AS PayrollFrom,
    (CONVERT([date],dateadd(day,(-1),dateadd(month,datediff(month,(0),getdate())+(1),(0))))) AS PayrollTo,
    (datename(month,getdate())) + ', ' + (datename(year,getdate())) AS PayrollPeriod,
    (Salary/[dbo].[totalWorkingDaysScalar](StaffId, datename(month,getdate()))) * [dbo].[totalDaysWorkedScalar](StaffId, datename(month,getdate())) as GrossSalary,
              .10 as IncomeTaxFactor,
              ((Salary/[dbo].[totalWorkingDaysScalar](StaffId, datename(month,getdate()))) * [dbo].[totalDaysWorkedScalar](StaffId, datename(month,getdate()))) - ((Salary/[dbo].[totalWorkingDaysScalar](StaffId, datename(month,getdate()))) * [dbo].[totalDaysWorkedScalar](StaffId, datename(month,getdate())) * (.10)) as TotalNetSalary

FROM            Staff
where Staff.StaffId=@StaffId
END


GO

I want the same behavior but without manually providing input. I want it to fetch all existing staffId from staff table all by itslef with staffId still being the sole parameter. How do I do that? I have tried doing it by using a WHILE loop. But it

set @StaffId = select Staff.StaffId from Staff

is showing squiggly lines on select

Please help. I am using SQL server 2014.

Upvotes: 0

Views: 490

Answers (2)

Nolan Shang
Nolan Shang

Reputation: 2328

I think process each staff with loop is not good performance.

you can modify you stored procedure to support process multiple staffs For example, you can change the parameter @staffID to a varchar variable to get a string of combin multliple staffid with a delimiter

FROM   Staff
where Staff.StaffId=isnull(@StaffIds,Staff.StaffId)  --NULL is process all data
      or case when @StaffIds is null then 1 else 
charindex(','+convert(varchar,Staff.StaffId)+',',','+@StaffIds+',') end >0

Upvotes: 0

3N1GM4
3N1GM4

Reputation: 3361

If you want to return all of the records from your Staff table every time and you want the exact same query to be executed every time, then you don't need a function at all, just create a View:

CREATE VIEW dbo.CurrentMonthPayroll
AS
    SELECT  StaffId, 
            Name, 
            CNICNumber, 
            Salary, 
            BankAccountNumber,
            [dbo].[totalWorkingDaysScalar](StaffId, DATENAME(MONTH, GETDATE())) AS TotalWorkingDays, 
            [dbo].[totalDaysWorkedScalar](StaffId, DATENAME(MONTH, GETDATE())) AS TotalDaysWorked,
            (CONVERT(DATE,GETDATE())) AS PayrollDate,
            (CONVERT(DATE,DATEADD(MONTH,(-1),DATEADD(MONTH,DATEDIFF(MONTH,(0),GETDATE())+(1),(0))))) AS PayrollFrom,
            (CONVERT(DATE,DATEADD(DAY,(-1),DATEADD(MONTH,DATEDIFF(MONTH,(0),GETDATE())+(1),(0))))) AS PayrollTo,
            (DATENAME(MONTH,GETDATE())) + ', ' + (DATENAME(YEAR,GETDATE())) AS PayrollPeriod,
            (Salary/[dbo].[totalWorkingDaysScalar](StaffId, DATENAME(MONTH,GETDATE()))) * [dbo].[totalDaysWorkedScalar](StaffId, DATENAME(MONTH,GETDATE())) AS GrossSalary,
            .10 AS IncomeTaxFactor,
            ((Salary/[dbo].[totalWorkingDaysScalar](StaffId, DATENAME(MONTH,GETDATE()))) * [dbo].[totalDaysWorkedScalar](StaffId, DATENAME(MONTH,GETDATE()))) - ((Salary/[dbo].[totalWorkingDaysScalar](StaffId, DATENAME(MONTH,GETDATE()))) * [dbo].[totalDaysWorkedScalar](StaffId, DATENAME(MONTH,GETDATE())) * (.10)) AS TotalNetSalary
    FROM Staff
GO

If you want to INSERT these records into some other table, you can:

INSERT INTO PayrollTable
SELECT StaffId,
Name,
CNICNumber,
Salary,
BankAccountNumber,
TotalWorkingDays,
TotalDaysWorked,
PayrollDate,
PayrollFrom,
PayrollTo,
PayrollPeriod,
GrossSalary,
IncomeTaxFactor,
TotalNetSalary
FROM dbo.CurrentMonthPayroll

Upvotes: 2

Related Questions