Need assistance modifying a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Need assistance modifying a stored procedure

Author

Topic

GreenWizard
Starting Member

2 Posts

Posted - 2014-12-11 : 10:31:53

I've been tasked to modify a stored procedure/crystal report, however I'm a beginner when it comes to SQL.

Below is the code. All I need to do is add the table column "PAYEE" from table CHECK_HDR. It seems simple, but I worry I'm going to mess up the database if I make a mistake, and thus I was hoping for some guidance before I make any changes to the code. I'm going to test it out in our test database first, but I'm still looking for reassurance.

That said, what should be added to the code, and where, so that the query selects the Payee also?

Thank you so much!

CODE:

USE [DFRS]
GO
/****** Object: StoredProcedure [dbo].[CrystalCheckRun] Script Date: 1/1/2014 12:00:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[CrystalCheckRun]
@START_CHECK_DATE AS DATETIME,
@END_CHECK_DATE AS DATETIME
AS
/*
** ObjectName: CrystalCheckRun
**
** Project:
** SubProject:
** FileName: CrystalCheckRun.sql
** Type: Stored Procedure
**
** Description: Check Run
**
** Revision History
** ----------------------------------------------------------------------------
** Date Name Description
** ----------------------------------------------------------------------------
**2014-01-01
**
**
*/
/*
DECLARE @START_CHECK_DATE AS DATETIME,
@END_CHECK_DATE AS DATETIME

SET @START_CHECK_DATE = '2014-01-01'
SET @END_CHECK_DATE = '2014-01-31'
*/
SET @START_CHECK_DATE = CONVERT(DATETIME, CAST(@START_CHECK_DATE AS VARCHAR(11)))
SET @END_CHECK_DATE = @END_CHECK_DATE + 1
SET @END_CHECK_DATE = CONVERT(DATETIME, CAST(@END_CHECK_DATE AS VARCHAR(11)))

/*SELECT @START_CHECK_DATE,
@END_CHECK_DATE
*/
SELECT H.ACCOUNT_NO AS 'ACCOUNT NUMBER',
(CASE H.DOC_STATUS WHEN '13' THEN 'POSTED (GL)' WHEN '11' THEN 'VOID' ELSE 'UNKNOWN' END) AS 'VOID INDICATOR',
H.CHECK_NO AS 'CHECK NUMBER',
(CASE H.AMOUNT WHEN '0' THEN H.ORIG_AMOUNT ELSE H.AMOUNT END) AS 'AMOUNT',
H.CHECK_DATE AS 'ISSUE DATE',
H.ACCTNO
FROM GDB_01_001DFRS3.dbo.CHECK_HDR H
LEFT JOIN GDB_01_001DFRS3.dbo.CUSTVEND CV ON (H.ACCTNO = CV.ACCTNO AND H.SUBC = CV.SUBC)
WHERE H.DOC_TYPE = 'AP'
AND H.DOC_CATEGORY <> 'C6'
AND H.DOC_STATUS <> '11'
AND (H.DOC_CATEGORY IN ('C1'))
AND (((H.BANK_ID = '03') and (H.ACCOUNT_NO = '98301979881')))
AND (H.CHECK_DATE >= @START_CHECK_DATE and H.CHECK_DATE < @END_CHECK_DATE)

UNION ALL

SELECT H.ACCOUNT_NO AS 'ACCOUNT NUMBER',
(CASE H.DOC_STATUS WHEN '13' THEN 'POSTED (GL)' WHEN '11' THEN 'VOID' ELSE 'UNKNOWN'END) AS 'VOID INDICATOR',
H.CHECK_NO AS 'CHECK NUMBER',
(CASE H.AMOUNT WHEN '0' THEN H.ORIG_AMOUNT ELSE H.AMOUNT END) AS 'AMOUNT',
H.CHECK_DATE AS 'ISSUE DATE',
H.ACCTNO
FROM GDB_01_001DFRS3.dbo.CHECK_HDR H
LEFT JOIN GDB_01_001DFRS3.dbo.CUSTVEND CV ON (H.ACCTNO = CV.ACCTNO AND H.SUBC = CV.SUBC)
WHERE H.DOC_TYPE = 'AP'
AND H.DOC_CATEGORY <> 'C6'
AND H.DOC_STATUS = '11'
AND (H.DOC_CATEGORY IN ('C1'))
AND (((H.BANK_ID = '03') and (H.ACCOUNT_NO = '98301979881')))
AND (H.CHECK_DATE >= @START_CHECK_DATE and H.CHECK_DATE < @END_CHECK_DATE)

ORDER BY ACCTNO ASC

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-12-11 : 11:44:48

USE [DFRS]
GO
/****** Object: StoredProcedure [dbo].[CrystalCheckRun] Script Date: 1/1/2014 12:00:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[CrystalCheckRun]
@START_CHECK_DATE AS DATETIME,
@END_CHECK_DATE AS DATETIME
AS
/*
** ObjectName: CrystalCheckRun
**
** Project:
** SubProject:
** FileName: CrystalCheckRun.sql
** Type: Stored Procedure
**
** Description: Check Run
**
** Revision History
** ----------------------------------------------------------------------------
** Date Name Description
** ----------------------------------------------------------------------------
**2014-01-01
**
**
*/
/*
DECLARE @START_CHECK_DATE AS DATETIME,
@END_CHECK_DATE AS DATETIME

SET @START_CHECK_DATE = '2014-01-01'
SET @END_CHECK_DATE = '2014-01-31'
*/
SET @START_CHECK_DATE = CONVERT(DATETIME, CAST(@START_CHECK_DATE AS VARCHAR(11)))
SET @END_CHECK_DATE = @END_CHECK_DATE + 1
SET @END_CHECK_DATE = CONVERT(DATETIME, CAST(@END_CHECK_DATE AS VARCHAR(11)))

/*SELECT @START_CHECK_DATE,
@END_CHECK_DATE
*/
SELECT H.ACCOUNT_NO AS 'ACCOUNT NUMBER',
(CASE H.DOC_STATUS WHEN '13' THEN 'POSTED (GL)' WHEN '11' THEN 'VOID' ELSE 'UNKNOWN' END) AS 'VOID INDICATOR',
H.CHECK_NO AS 'CHECK NUMBER',
(CASE H.AMOUNT WHEN '0' THEN H.ORIG_AMOUNT ELSE H.AMOUNT END) AS 'AMOUNT',
H.CHECK_DATE AS 'ISSUE DATE',
H.ACCTNO,
H.PAYEE
FROM GDB_01_001DFRS3.dbo.CHECK_HDR H
LEFT JOIN GDB_01_001DFRS3.dbo.CUSTVEND CV ON (H.ACCTNO = CV.ACCTNO AND H.SUBC = CV.SUBC)
WHERE H.DOC_TYPE = 'AP'
AND H.DOC_CATEGORY <> 'C6'
AND H.DOC_STATUS <> '11'
AND (H.DOC_CATEGORY IN ('C1'))
AND (((H.BANK_ID = '03') and (H.ACCOUNT_NO = '98301979881')))
AND (H.CHECK_DATE >= @START_CHECK_DATE and H.CHECK_DATE < @END_CHECK_DATE)

UNION ALL

SELECT H.ACCOUNT_NO AS 'ACCOUNT NUMBER',
(CASE H.DOC_STATUS WHEN '13' THEN 'POSTED (GL)' WHEN '11' THEN 'VOID' ELSE 'UNKNOWN'END) AS 'VOID INDICATOR',
H.CHECK_NO AS 'CHECK NUMBER',
(CASE H.AMOUNT WHEN '0' THEN H.ORIG_AMOUNT ELSE H.AMOUNT END) AS 'AMOUNT',
H.CHECK_DATE AS 'ISSUE DATE',
H.ACCTNO,
H.PAYEE
FROM GDB_01_001DFRS3.dbo.CHECK_HDR H
LEFT JOIN GDB_01_001DFRS3.dbo.CUSTVEND CV ON (H.ACCTNO = CV.ACCTNO AND H.SUBC = CV.SUBC)
WHERE H.DOC_TYPE = 'AP'
AND H.DOC_CATEGORY <> 'C6'
AND H.DOC_STATUS = '11'
AND (H.DOC_CATEGORY IN ('C1'))
AND (((H.BANK_ID = '03') and (H.ACCOUNT_NO = '98301979881')))
AND (H.CHECK_DATE >= @START_CHECK_DATE and H.CHECK_DATE < @END_CHECK_DATE)

ORDER BY ACCTNO ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources