I'm really a Crystal Reports programmer and am trying to start using T SQL more to write reports using stored procedures. I have the stored procedure for my report but I need to use another stored procedure to get the product name. I have tried using CALL and EXEC. Can anyone tell me what I am doig wrong?
Here is the SP for my report:
CREATE PROCEDURE [dbo].[SP_SS_Payroll_BYEmpNumber] AS
SELECT TK_LABOR_0506.TK_LABOR_JOB_CODE, TK_LABOR_0506.TK_LABOR_NUM_RET_STUDENTS,
TK_LABOR_0506.TK_LABOR_HRS_ENT, TK_LABOR_0506.TK_LABOR_NUM_STUDENTS, TK_LABOR_0506.TK_LABOR_RATE,
TK_LABOR_0506.TK_LABOR_AMT, TK_LABOR_0506.TK_LABOR_REQ_PRO, TK_LABOR_0506.TK_LABOR_COST_CENTER_CD,
TK_LABOR_0506.TK_LABOR_OBJECT_CD, TK_LABOR_0506.TK_LABOR_SUB_LEDGER_CD, PERSON.PER_LAST_NM,
TIME_KEEPING_TRAN_0506.TK_LESS_DT, TK_LABOR_0506.TK_TYPE, PERSON.PER_FIRST_NM, TIME_KEEPING_TRAN_0506.PRO_PER_ID,
HUMAN_RESOURCE.HR_ID
FROM ((skischool.dbo.time_keeping_tran TIME_KEEPING_TRAN_0506 INNER JOIN skischool.dbo.tk_labor TK_LABOR_0506 ON
TIME_KEEPING_TRAN_0506.TK_ID=TK_LABOR_0506.TK_ID) LEFT OUTER JOIN skischool.dbo.PERSON PERSON ON
TIME_KEEPING_TRAN_0506.PRO_PER_ID=PERSON.PER_ID) LEFT OUTER JOIN skischool.dbo.HUMAN_RESOURCE HUMAN_RESOURCE ON
PERSON.PER_ID=HUMAN_RESOURCE.PER_ID
WHERE (TIME_KEEPING_TRAN_0506.TK_LESS_DT>={ts '2006-11-01 00:00:00'} AND
TIME_KEEPING_TRAN_0506.TK_LESS_DT<{ts '2006-11-28 00:00:00'})
GO
The where Statement will come out when done testing.
The stored procedure I am trying to call is named
asc_f_get_prod_nm_a
The SP I wrote using exec looks like this:
CREATE PROCEDURE [dbo].[SP_SS_Payroll_BYEmpNumber] AS
EXEC skischool.dbo.asc_f_get_prod_nm_a
SELECT TK_LABOR_0506.TK_LABOR_JOB_CODE, TK_LABOR_0506.TK_LABOR_NUM_RET_STUDENTS,
TK_LABOR_0506.TK_LABOR_HRS_ENT, TK_LABOR_0506.TK_LABOR_NUM_STUDENTS, TK_LABOR_0506.TK_LABOR_RATE,
TK_LABOR_0506.TK_LABOR_AMT, TK_LABOR_0506.TK_LABOR_REQ_PRO, TK_LABOR_0506.TK_LABOR_COST_CENTER_CD,
TK_LABOR_0506.TK_LABOR_OBJECT_CD, TK_LABOR_0506.TK_LABOR_SUB_LEDGER_CD, PERSON.PER_LAST_NM,
TIME_KEEPING_TRAN_0506.TK_LESS_DT, TK_LABOR_0506.TK_TYPE, PERSON.PER_FIRST_NM, TIME_KEEPING_TRAN_0506.PRO_PER_ID,
HUMAN_RESOURCE.HR_ID
FROM ((skischool.dbo.time_keeping_tran TIME_KEEPING_TRAN_0506 INNER JOIN skischool.dbo.tk_labor TK_LABOR_0506 ON
TIME_KEEPING_TRAN_0506.TK_ID=TK_LABOR_0506.TK_ID) LEFT OUTER JOIN skischool.dbo.PERSON PERSON ON
TIME_KEEPING_TRAN_0506.PRO_PER_ID=PERSON.PER_ID) LEFT OUTER JOIN skischool.dbo.HUMAN_RESOURCE HUMAN_RESOURCE ON
PERSON.PER_ID=HUMAN_RESOURCE.PER_ID
WHERE (TIME_KEEPING_TRAN_0506.TK_LESS_DT>={ts '2006-11-01 00:00:00'} AND
TIME_KEEPING_TRAN_0506.TK_LESS_DT<{ts '2006-11-28 00:00:00'})
GO
It will run in Crystal with this as my data source but the product name is not pulled in (available). Do I have the exec command in the wrong place?
Thanks for any help you can offer.
Here is the SP for my report:
CREATE PROCEDURE [dbo].[SP_SS_Payroll_BYEmpNumber] AS
SELECT TK_LABOR_0506.TK_LABOR_JOB_CODE, TK_LABOR_0506.TK_LABOR_NUM_RET_STUDENTS,
TK_LABOR_0506.TK_LABOR_HRS_ENT, TK_LABOR_0506.TK_LABOR_NUM_STUDENTS, TK_LABOR_0506.TK_LABOR_RATE,
TK_LABOR_0506.TK_LABOR_AMT, TK_LABOR_0506.TK_LABOR_REQ_PRO, TK_LABOR_0506.TK_LABOR_COST_CENTER_CD,
TK_LABOR_0506.TK_LABOR_OBJECT_CD, TK_LABOR_0506.TK_LABOR_SUB_LEDGER_CD, PERSON.PER_LAST_NM,
TIME_KEEPING_TRAN_0506.TK_LESS_DT, TK_LABOR_0506.TK_TYPE, PERSON.PER_FIRST_NM, TIME_KEEPING_TRAN_0506.PRO_PER_ID,
HUMAN_RESOURCE.HR_ID
FROM ((skischool.dbo.time_keeping_tran TIME_KEEPING_TRAN_0506 INNER JOIN skischool.dbo.tk_labor TK_LABOR_0506 ON
TIME_KEEPING_TRAN_0506.TK_ID=TK_LABOR_0506.TK_ID) LEFT OUTER JOIN skischool.dbo.PERSON PERSON ON
TIME_KEEPING_TRAN_0506.PRO_PER_ID=PERSON.PER_ID) LEFT OUTER JOIN skischool.dbo.HUMAN_RESOURCE HUMAN_RESOURCE ON
PERSON.PER_ID=HUMAN_RESOURCE.PER_ID
WHERE (TIME_KEEPING_TRAN_0506.TK_LESS_DT>={ts '2006-11-01 00:00:00'} AND
TIME_KEEPING_TRAN_0506.TK_LESS_DT<{ts '2006-11-28 00:00:00'})
GO
The where Statement will come out when done testing.
The stored procedure I am trying to call is named
asc_f_get_prod_nm_a
The SP I wrote using exec looks like this:
CREATE PROCEDURE [dbo].[SP_SS_Payroll_BYEmpNumber] AS
EXEC skischool.dbo.asc_f_get_prod_nm_a
SELECT TK_LABOR_0506.TK_LABOR_JOB_CODE, TK_LABOR_0506.TK_LABOR_NUM_RET_STUDENTS,
TK_LABOR_0506.TK_LABOR_HRS_ENT, TK_LABOR_0506.TK_LABOR_NUM_STUDENTS, TK_LABOR_0506.TK_LABOR_RATE,
TK_LABOR_0506.TK_LABOR_AMT, TK_LABOR_0506.TK_LABOR_REQ_PRO, TK_LABOR_0506.TK_LABOR_COST_CENTER_CD,
TK_LABOR_0506.TK_LABOR_OBJECT_CD, TK_LABOR_0506.TK_LABOR_SUB_LEDGER_CD, PERSON.PER_LAST_NM,
TIME_KEEPING_TRAN_0506.TK_LESS_DT, TK_LABOR_0506.TK_TYPE, PERSON.PER_FIRST_NM, TIME_KEEPING_TRAN_0506.PRO_PER_ID,
HUMAN_RESOURCE.HR_ID
FROM ((skischool.dbo.time_keeping_tran TIME_KEEPING_TRAN_0506 INNER JOIN skischool.dbo.tk_labor TK_LABOR_0506 ON
TIME_KEEPING_TRAN_0506.TK_ID=TK_LABOR_0506.TK_ID) LEFT OUTER JOIN skischool.dbo.PERSON PERSON ON
TIME_KEEPING_TRAN_0506.PRO_PER_ID=PERSON.PER_ID) LEFT OUTER JOIN skischool.dbo.HUMAN_RESOURCE HUMAN_RESOURCE ON
PERSON.PER_ID=HUMAN_RESOURCE.PER_ID
WHERE (TIME_KEEPING_TRAN_0506.TK_LESS_DT>={ts '2006-11-01 00:00:00'} AND
TIME_KEEPING_TRAN_0506.TK_LESS_DT<{ts '2006-11-28 00:00:00'})
GO
It will run in Crystal with this as my data source but the product name is not pulled in (available). Do I have the exec command in the wrong place?
Thanks for any help you can offer.