Help with PROC

Transact-SQL
1

Hello,

Can you please assist with this PROC. I am getting the following error:

Msg 102, Level 15, State 1, Procedure spEmployeeDDA, Line 27
Incorrect syntax near 'EMPLOYEE'.

Here is the SQL --->

CREATE PROCEDURE [dbo].[spEmployeeDDA]
-- Add the parameters for the stored procedure here
AS
BEGIN

DECLARE @EmpDDA table (
	employee varchar(10) NULL,
	LName varchar(20) Null,
	FName varchar (20) NULL,
	Bank varchar(32) NULL,
	AccountNumber decimal(12,5),
	DepositAmount decimal(12,5),
	CheckDate datetime,
	EarnDed varchar(5),
	HoursWorked decimal(12,5)
	)

INSERT INTO @EmpDDA (employee, LName, FName, Bank, AccountNumber, DepositAmount, CheckDate, EarnDed, HoursWorked)
SELECT e.employee, e.lastname, e.firstname, t.BKACCTDESC, t.acctnum, t.DEPOSITAMT, h.transdate, d.EARNDED, D.HOURS

  FROM SageHRMS_GTM..UPCHKD D
          INNER Join SageHRMS_GTM..UPEMPL e
          on d.EMPLOYEE = e.EMPLOYEE
		  INNER Join SageHRMS_GTM..UPETRN t
          on d.EMPLOYEE = t.EMPLOYEE
		  INNER Join SageHRMS_GTM..UPCHKH h
          on t.EMPLOYEE = h.EMPLOYEE
2

Try adding an END GO at the end of your procedure.

3

SMDH.

Thanks so much :smile:

4

You're quite welcome. It happens to the best of us.

5

You'll have to excuse my terrible SQL. Not sure if you can, but any further help would be great on this query. When I run the select, I am getting waaaay too many records. I'm basically trying to create a query for a payroll system that will give our users some basic information on thier check. Hours, earnings, name, ect...

The select statement pulls in tons and tons of records, I think the join is incorrect. I really just want to be able to put a check date into the parameter and pull the declared table columns for that one check date. Here is the same query but with a @CheckDate parameter. Again, I'm not sure you can help since you don't know the data, but any is alway much appreciated.

CREATE PROCEDURE [dbo].[spEmployeeDDA_v2]
-- Add the parameters for the stored procedure here

@CheckDate datetime

AS
BEGIN

DECLARE @EmpDDA table (
employee varchar(10) NULL,
LName varchar(20) Null,
FName varchar (20) NULL,
DepositAmount decimal(12,5),
CheckDate datetime,
EarnDed varchar(5),
HoursWorked decimal(12,5)
)
INSERT INTO @EmpDDA (employee, LName, FName, DepositAmount, CheckDate, EarnDed, HoursWorked)
SELECT e.employee, e.lastname, e.firstname, t.DEPOSITAMT, h.transdate, d.EARNDED, D.HOURS

FROM SageHRMS_GTM..UPEMPL e
INNER Join SageHRMS_GTM..UPCHKD d
on e.EMPLOYEE = d.EMPLOYEE
INNER Join SageHRMS_GTM..UPETRN t
on d.EMPLOYEE = t.DEPOSITAMT
INNER Join SageHRMS_GTM..UPCHKH h
on t.EMPLOYEE = h.EMPLOYEE

	  end 
	  go
6

As you suggested, I am not able to suggest the changes you should make because I don't know the data. However, you can debug it yourself by following a step by step procedure.

To do this, isolate one employee for which you are getting multiple records, but should be getting only one record. Then run a query against the UPEMPL table with a where clause that filters for only that employee. For example:

SELECT
	*
FROM
	SageHRMS_GTM..UPEMPL e
WHERE
	e.Employee = 'XYZ'

Presumably, you should get only one record, if there is only one row for a given employee in the UPEMPL table. Now, add in the second table.

SELECT
	*
FROM
	SageHRMS_GTM..UPEMPL e
	INNER JOIN SageHRMS_GTM..UPCHKD d ON e.EMPLOYEE = d.EMPLOYEE
WHERE
	e.Employee = 'XYZ'

When you do this if you still get only one row, you have the correct join condition. If you get more than one row, look at those rows, and see which of those rows you should be getting. Then, figure out what is the criterion that makes that one row in the UPCHKD table that makes it the candidate row that you want. Based on that, add something more to the join condition.

Repeat this process until you get only one row with all the tables joined together.

7

Thanks for the tips. I went through and ran those queries. So for the second query, I replaced XYZ with an employee number and it pulled in all the check detail records. Each check can have multiple records, because a person can have different earning codes within one pay period. There is a column in the UPCHKD called PEREND. So for Employee '10' with a PEREND of 20150705, they have 5 records. I want to pull these 5 records in only. Let me know if this is not clear or if I need to add more information. Thanks again.

Mike

8

Is it that you want to select all the records that have the highest PEREND? Or is it always records that have PEREND = 20140705?

Assuming that you want to pick the highest PEREND, write the query like this. That should give you the 5 records.

SELECT
	*
FROM
	SageHRMS_GTM..UPEMPL e
	CROSS APPLY
	(
		SELECT TOP (1) WITH TIES *
		FROM SageHRMS_GTM..UPCHKD d
		WHERE e.EMPLOYEE = d.EMPLOYEE
		ORDER BY PEREND DESC -- picking only records that have the highest value of PEREND
	) d
WHERE
	e.Employee = 'XYZ'
9

That's it. That pulls in the 5 records for that employee.

Thanks for all your support today. I may need some more tweaks tomorrow so we'll see.

Mike