How to get data from Stored Proc?
Posted: Thu Jul 25, 2024 3:45 am
Hi, I have a stored proc that creates a temporary table, pulls data from various existing tables into the temporary table, and does some processing. In the end it selects the temporary table and displays it. Stimulsoft correctly picks up the parameters required by the stored proc, but does not pick up the columns in the resulting table displayed by the stored proc at the end of the run.
Can I know how to make Stimulsoft see the table generated by the stored proc at the end of the run?
The code of the Stored Proc is as follows, am I missing something?
Can I know how to make Stimulsoft see the table generated by the stored proc at the end of the run?
The code of the Stored Proc is as follows, am I missing something?
Code: Select all
CREATE PROCEDURE [dbo].[WeeklyCollectDeliverJobReport]
(
@month INT,
@year INT,
@CustList NVARCHAR(MAX),
@Activity nchar(1)
)
AS
BEGIN
DECLARE @DateStart DATETIME;
DECLARE @DateEnd DATETIME;
DECLARE @RealDateStart DATETIME;
DECLARE @RealDateEnd DATETIME;
DECLARE @TimeSpan INT;
SET @DateStart = CAST(@year AS nvarchar(MAX))+'-'+CAST(@month AS nvarchar(MAX))+'-01 00:00:00';
SET @DateEnd = DATEADD(DAY, 1, EOMONTH(@DateStart));
SET @RealDateStart=IIF(DATEPART(dw, @DateStart) > 1, DATEADD(DAY, 8 - DATEPART(dw, @DateStart), @DateStart), @DateStart);
SET @RealDateEnd=DATEADD(DAY, 7 - DATEPART(dw,@DateEnd), @DateEnd);
SET @TimeSpan = DATEDIFF(DAY, @RealDateEnd, @RealDateEnd);
CREATE TABLE #JobsCollect
(
JobNo NVARCHAR(MAX) NULL,
JobDateBand INT NULL,
JobDateString NVARCHAR(MAX) NULL,
JobDate DateTime NULL,
CustCode NVARCHAR(4) NULL,
RequestDate DateTime NULL,
ServiceType NVARCHAR(MAX) NULL,
MediaType NVARCHAR(MAX) NULL,
JobType NVARCHAR(2) NULL,
Remarks NVARCHAR(MAX) NULL,
TotalCtn INT NULL,
TotalMat INT NULL,
Staff NVARCHAR(MAX) NULL
)
IF LOWER(@Activity)='c'
BEGIN
INSERT INTO #JobsCollect
SELECT
j.OldJobNo
, 1 + (DATEDIFF(DAY, @RealDateStart, DATEADD(day, ((DATEPART(WEEKDAY,j.JobDate) * -1) +1), CAST (j.JobDate AS DATE))) / 7) AS 'JobDateBand'
, CAST(DATEADD(day, ((DATEPART(WEEKDAY,j.JobDate) * -1) +1), CAST (j.JobDate AS DATE)) AS NVARCHAR(MAX))
+ ' - '
+ CAST(DATEADD(day, 7 - (DATEPART(WEEKDAY, j.JobDate)), CAST (j.JobDate AS DATE)) AS NVARCHAR(MAX)) AS 'JobDateString'
, j.JobDate
, j.CustCode
, j.RequestDate
, CASE WHEN ServLevel='F' THEN 'FAX'
WHEN ServLevel='T' THEN 'PHONE'
ELSE (SELECT SrvLevel FROM JobServiceLevel s WHERE s.SvrID=j.ServLevel) END AS 'ServiceType'
, (SELECT Description FROM CartonType c WHERE c.CtnType=j.CtnType) As 'MediaType'
, j.JobType
, j.Remark
, j.TotalCtn
, REPLACE(j.TamperSealQty,null,0)
+REPLACE(j.PlasticBagQty,null,0)
+REPLACE(j.RICQty,null,0) AS 'TotalMat'
, j.Staff
FROM Job j
WHERE j.JobType LIKE '%C%'
AND CustCode IN (SELECT * FROM STRING_SPLIT(@CustList,','))
AND j.JobDate BETWEEN @RealDateStart AND @RealDateEnd
END
ELSE IF LOWER(@Activity)='d'
BEGIN
INSERT INTO #JobsCollect
SELECT
j.OldJobNo
, 1 + (DATEDIFF(DAY, @RealDateStart, DATEADD(day, ((DATEPART(WEEKDAY,j.JobDate) * -1) +1), CAST (j.JobDate AS DATE))) / 7) AS 'JobDateBand'
, CAST(DATEADD(day, ((DATEPART(WEEKDAY,j.JobDate) * -1) +1), CAST (j.JobDate AS DATE)) AS NVARCHAR(MAX))
+ ' - '
+ CAST(DATEADD(day, 7 - (DATEPART(WEEKDAY, j.JobDate)), CAST (j.JobDate AS DATE)) AS NVARCHAR(MAX)) AS 'JobDateString'
, j.JobDate
, j.CustCode
, j.RequestDate
, CASE WHEN ServLevel='F' THEN 'FAX'
WHEN ServLevel='T' THEN 'PHONE'
ELSE (SELECT SrvLevel FROM JobServiceLevel s WHERE s.SvrID=j.ServLevel) END AS 'ServiceType'
, (SELECT Description FROM CartonType c WHERE c.CtnType=j.CtnType) As 'MediaType'
, j.JobType
, j.Remark
, j.TotalCtn
, REPLACE(j.TamperSealQty,null,0)
+REPLACE(j.PlasticBagQty,null,0)
+REPLACE(j.RICQty,null,0) AS 'TotalMat'
, j.Staff
FROM Job j
WHERE j.JobType LIKE '%D%'
AND CustCode IN (SELECT * FROM STRING_SPLIT(@CustList,','))
AND j.JobDate BETWEEN @RealDateStart AND @RealDateEnd
END
ELSE
BEGIN
INSERT INTO #JobsCollect
SELECT
j.OldJobNo
, (DATEDIFF(DAY,DATEADD(day, ((DATEPART(WEEKDAY,j.JobDate) * -1) +1), CAST (j.JobDate AS DATE)), @RealDateStart) / 7) + 1 AS 'JobDateBand'
, CAST(DATEADD(day, ((DATEPART(WEEKDAY,j.JobDate) * -1) +1), CAST (j.JobDate AS DATE)) AS NVARCHAR(MAX))
+ ' - '
+ CAST(DATEADD(day, 7 - (DATEPART(WEEKDAY, j.JobDate)), CAST (j.JobDate AS DATE)) AS NVARCHAR(MAX)) AS 'JobDateString'
, j.JobDate
, j.CustCode
, j.RequestDate
, CASE WHEN ServLevel='F' THEN 'FAX'
WHEN ServLevel='T' THEN 'PHONE'
ELSE (SELECT SrvLevel FROM JobServiceLevel s WHERE s.SvrID=j.ServLevel) END AS 'ServiceType'
, (SELECT Description FROM CartonType c WHERE c.CtnType=j.CtnType) As 'MediaType'
, j.JobType
, j.Remark
, j.TotalCtn
, REPLACE(j.TamperSealQty,null,0)
+REPLACE(j.PlasticBagQty,null,0)
+REPLACE(j.RICQty,null,0) AS 'TotalMat'
, j.Staff
FROM Job j
WHERE CustCode IN (SELECT * FROM STRING_SPLIT(@CustList,','))
AND j.JobDate BETWEEN @RealDateStart AND @RealDateEnd
END
DECLARE @tempDate DATETIME = @RealDateStart;
DECLARE @counter int = 1;
WHILE (DATEADD(DAY, 6, @tempDate) <= @RealDateEnd)
BEGIN
IF (SELECT COUNT(JobNo) FROM #JobsCollect WHERE JobDate BETWEEN @tempDate AND DATEADD(DAY, 6, @tempDate))=0
BEGIN
INSERT INTO #JobsCollect (JobDateBand, JobDateString, Remarks) VALUES (
@counter, CAST(CAST(@tempDate AS DATE) AS NVARCHAR(MAX))+' - '+CAST(CAST(DATEADD(DAY,7,@tempDate) AS DATE) AS NVARCHAR(MAX)),
'<--- NIL ------>')
END
SET @counter = @counter + 1;
SET @tempDate = DATEADD(DAY,7,@tempDate);
END
SELECT * FROM #JobsCollect ORDER BY JobDateString
END