Share
Report
725Hi,
I have a simple stored procedure which populates two tables (which were created via dexterity). When I run the sp from SQL, the query runs properly and the tables populate. When I call it from dexterity (using a protype procedure etc or using pass through sql), the first table populates but the second is always empty.
Using SQL Profiler, I can see the sp being called and I know it runs because both tables truncate and the first populates as expected.
Can't fugure this out...
Stored Procedure
CREATE PROCEDURE RCL_SP_SUSPSTMNT
@TRANS VARCHAR(15)
AS
DECLARE @DATE DATETIME, @DATE2 DATETIME, @PERIOD VARCHAR(5), @BRANCH VARCHAR(6), @BB FLOAT
SET NOCOUNT ON
--CLEAR THE TABLE
TRUNCATE TABLE RCL0091
TRUNCATE TABLE RCL0090
-- THE DATA FOR THE SEARCH CRITERIA
SELECT @BRANCH = CUSTNMBR ,@DATE = POSTDATE
FROM RM20101
WHERE DOCNUMBR = @TRANS AND BACHNUMB = 'MBNOTES'
GROUP BY CUSTNMBR ,POSTDATE
--GET THE PERIOD ABBREV AND PERIOD END DATE
SELECT @PERIOD = RTRIM(CONVERT(CHAR, MONTH(@DATE))) +'/'+ CONVERT(CHAR, RIGHT(YEAR(@DATE),2))
SELECT @DATE2= RTRIM(CONVERT(CHAR, MONTH(@DATE))) +'/01/'+ CONVERT(CHAR, RIGHT(YEAR(@DATE),2))
--CALCULATE THE BEG BALANCE
SELECT CUSTNMBR,
CASE RMDTYPAL
WHEN 3 THEN 'DR'
WHEN 7 THEN 'CR' END AS TYPE,
CASE RMDTYPAL
WHEN 3 THEN ISNULL(SLSAMNT*-1,0)
WHEN 7 THEN ISNULL(SLSAMNT,0) END AS AMOUNT,
@PERIOD AS PERIOD
INTO #TMP
FROM RM20101
WHERE RMDTYPAL IN (3,7) AND SLPRSNID = 'MEMBERSHIP'
AND POSTDATE < @DATE2 AND CUSTNMBR = @BRANCH
--INSERT INTO TABLE
INSERT INTO RCL0090 (CRG_BRANCH,PERIOD, PERDBLNC)
SELECT CUSTNMBR, @PERIOD,SUM(AMOUNT)
FROM #TMP
GROUP BY CUSTNMBR
--GET THE PERAMETERS FOR THE NEXT TABLE
SELECT @BB = PERDBLNC
FROM RCL0090
WHERE CRG_BRANCH = @BRANCH
AND PERIOD =@PERIOD
--PULL THE SUSPENSE STATEMENT FOR THE TRANSMITTAL - ALL TRANSMITTALS FOR THE SAME BRANCH, IN THE SAME PERIOD, AND BEFORE THE DATE ON THE TRANSMITTAL
--QUERIED
INSERT INTO RCL0091
(CRG_BRANCH, DOCNUMBR, DOCDATE, TOTAL, PERDBLNC)
SELECT RM20101.CUSTNMBR, DOCNUMBR, POSTDATE, CASE WHEN RMDTYPAL = 3 THEN CURTRXAM *-1 ELSE CURTRXAM END AS AMOUNT, @BB AS OPENING
FROM RM20101 INNER JOIN RM00101 ON RM00101.CUSTNMBR = RM20101.CUSTNMBR
WHERE (RMDTYPAL IN (7, 3))
AND POSTDATE >= @DATE2 AND POSTDATE <= @DATE
AND BACHNUMB = 'MBNOTES'
AND RM20101.CUSTNMBR = @BRANCH
GO
The Profiler Output (using pass through SQL)
set ansi_nulls off set ansi_padding off set ansi_warnings off set concat_null_yields_null off set quoted_identifier off
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN SET DATEFORMAT ymd
SET NOCOUNT ON
SET CURSOR_CLOSE_ON_COMMIT OFF
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
SET ANSI_NULL_DFLT_ON ON
SET ANSI_PADDING OFF
SET NUMERIC_ROUNDABORT OFF
END
RCL..RCL_SP_SuspStmnt 'BC12406'
Thanks!
Catherine
*This post is locked for comments
All responses (
Answers (
