New to SQL Server 2000 2

[FJAY]

I have three SP (with date as the input parameter) and I will like to create a single SP by combining all three SP. Is this possible, if yes how do I go about doing this. Thanks.

Note:
The logic is to execute each SP and store the result in a temporary buffer and then use a select statement to create the final result.

 

[donutman]

Yes, it's probably possible. Let's see the 3 SPs. But let me ask what advantage do you expect from combining them?
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[FJAY]

I HAVE TO PASS IN PARAMETERS TO MY VIEWS ANYTIME I NEED TO RUN THIS REPORT.

SP ONE:
CREATE PROCEDURE dbo.[Medtox_Sales_Accts_Rec_Aging_Report_0]

-- DATE INPUT PARAMETERS - END DATE
@EndDate DateTime
AS

--NUMBER OF ROWS AFFECTED BY THE T-SQL IS NOT RETRUNED
SET NOCOUNT ON

--******************VARIABLES DECLARATIONS****************

--DECLARE VARIABLES TEMP_MEDTOX_AR_AGING_REPORT_1
DECLARE @INVOICE_ID VARCHAR (15),
@POSTING_DATE DATETIME,
@AMOUNT DECIMAL (15,2),
@STATUS CHAR (1)

--******************START OF TEMP_MEDTOX_AR_AGING_REPORT_1
--DELETE TEMP TABLE IF PREVIOUSLY CREATED
--DROP TABLE #TEMP_MEDTOX_AR_AGING_REPORT_1

-- CREATE TEMP TABLE - TEMP_MEDTOX_AR_AGING_REPORT_1
CREATE TABLE #TEMP_MEDTOX_AR_AGING_REPORT_1
(INVOICE_ID VARCHAR (15),
POSTING_DATE DATETIME,
AMOUNT DECIMAL (15,2),
STATUS CHAR (1))

--CLEAR TEMP TABLE IF PREVIOUSLY USED
DELETE FROM #TEMP_MEDTOX_AR_AGING_REPORT_1

--DECLARE CURSOR FOR TEMP TABLE - TEMP_MEDTOX_AR_AGING_REPORT_1
DECLARE CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_1 CURSOR

FOR SELECT TOP 100 PERCENT dbo.CASH_RECEIPT_LINE.INVOICE_ID, dbo.CASH_RECEIPT.POSTING_DATE,
dbo.CASH_RECEIPT_LINE.AMOUNT + dbo.CASH_RECEIPT_LINE.DISCOUNT_APPLIED AS AMOUNT, dbo.CASH_RECEIPT.STATUS
FROM dbo.CASH_RECEIPT INNER JOIN
dbo.CASH_RECEIPT_LINE ON dbo.CASH_RECEIPT.CUSTOMER_ID = dbo.CASH_RECEIPT_LINE.CUSTOMER_ID AND
dbo.CASH_RECEIPT.CHECK_ID = dbo.CASH_RECEIPT_LINE.CHECK_ID
WHERE (dbo.CASH_RECEIPT.STATUS = 'A') AND (dbo.CASH_RECEIPT.POSTING_DATE <= CONVERT(DATETIME, @ENDDATE, 102)) AND
(dbo.CASH_RECEIPT_LINE.INVOICE_ID IS NOT NULL)
ORDER BY dbo.CASH_RECEIPT_LINE.INVOICE_ID

--OPEN CURSOR
OPEN CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_1

--FETCH FROM CUSRSOR INTO THE DECLARED VARIABLES
FETCH NEXT FROM CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_1 INTO @INVOICE_ID, @POSTING_DATE, @AMOUNT, @STATUS
--INSERT RETURNED VALUES INTO TEMP TABLE - TEMP_MEDTOX_AR_AGING_REPORT_1
INSERT INTO #TEMP_MEDTOX_AR_AGING_REPORT_1
VALUES(@INVOICE_ID, @POSTING_DATE, @AMOUNT, @STATUS)

WHILE @@FETCH_STATUS = 0

BEGIN
--FETCH FROM CUSRSOR INTO THE DECLARED VARIABLES
FETCH NEXT FROM CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_1 INTO @INVOICE_ID, @POSTING_DATE, @AMOUNT, @STATUS
--INSERT DATA INTO TEMP TABLE - TEMP_MEDTOX_AR_AGING_REPORT_1
INSERT INTO #TEMP_MEDTOX_AR_AGING_REPORT_1
VALUES(@INVOICE_ID, @POSTING_DATE, @AMOUNT, @STATUS)
END

--CLOSE CURSOR
CLOSE CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_1
--DEALLOCATE CURSOR
DEALLOCATE CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_1

--******************END OF TEMP_MEDTOX_AR_AGING_REPORT_1

--******************START OF TEMP_MEDTOX_AR_AGING_REPORT_0

SELECT INVOICE_ID, SUM(AMOUNT) AS CHECK_AMOUNT
FROM #TEMP_MEDTOX_AR_AGING_REPORT_1
GROUP BY INVOICE_ID
ORDER BY INVOICE_ID

--******************END OF TEMP_MEDTOX_AR_AGING_REPORT_0

SET NOCOUNT OFF
GO

SP TWO:

CREATE PROCEDURE dbo.[Medtox_Sales_Accts_Rec_Aging_Report_6]

-- DATE INPUT PARAMETERS - END DATE
@EndDate DateTime
AS

--NUMBER OF ROWS AFFECTED BY THE T-SQL IS NOT RETRUNED
SET NOCOUNT ON

--******************VARIABLES DECLARATIONS

--DECLARE VARIABLES TEMP_MEDTOX_AR_AGING_REPORT_5
DECLARE @APPLY_DATE DATETIME,
@INV_INVOICE_ID VARCHAR (15),
@APPLY_AMOUNT DECIMAL (15,2)

--******************START OF TEMP_MEDTOX_AR_AGING_REPORT_5 *

--DELETE TEMP TABLE IF PREVIOUSLY CREATED
--DROP TABLE #TEMP_MEDTOX_AR_AGING_REPORT_5

-- CREATE TEMP TABLE - TEMP_MEDTOX_AR_AGING_REPORT_5
CREATE TABLE #TEMP_MEDTOX_AR_AGING_REPORT_5
(APPLY_DATE DATETIME,
INV_INVOICE_ID VARCHAR(15),
APPLY_AMOUNT DECIMAL (15,2))

--CLEAR TEMP TABLE IF PREVIOUSLY USED
DELETE FROM #TEMP_MEDTOX_AR_AGING_REPORT_5

--DECLARE CURSOR FOR TEMP TABLE - TEMP_MEDTOX_AR_AGING_REPORT_5
DECLARE CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_5 CURSOR

FOR SELECT apply_date, INV_INVOICE_ID, APPLY_AMOUNT
FROM dbo.RECV_MEMO_APPLY
WHERE (apply_date <= CONVERT(DATETIME, @EndDate, 102))

--OPEN CURSOR
OPEN CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_5

--FETCH FROM CUSRSOR INTO THE DECLARED VARIABLES
FETCH NEXT FROM CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_5 INTO @APPLY_DATE, @INV_INVOICE_ID, @APPLY_AMOUNT

--INSERT RETURNED VALUES INTO TEMP TABLE - TEMP_MEDTOX_AR_AGING_REPORT_5
INSERT INTO #TEMP_MEDTOX_AR_AGING_REPORT_5
VALUES (@APPLY_DATE, @INV_INVOICE_ID, @APPLY_AMOUNT)

WHILE @@FETCH_STATUS = 0

BEGIN
--FETCH FROM CUSRSOR INTO THE DECLARED VARIABLES
FETCH NEXT FROM CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_5 INTO @APPLY_DATE, @INV_INVOICE_ID, @APPLY_AMOUNT
--INSERT DATA INTO TEMP TABLE - TEMP_MEDTOX_AR_AGING_REPORT_5
INSERT INTO #TEMP_MEDTOX_AR_AGING_REPORT_5
VALUES( @APPLY_DATE, @INV_INVOICE_ID, @APPLY_AMOUNT)
END

--CLOSE CURSOR
CLOSE CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_5
--DEALLOCATE CURSOR
DEALLOCATE CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_5

--******************END OF TEMP_MEDTOX_AR_AGING_REPORT_5 **

--******************START OF TEMP_MEDTOX_AR_AGING_REPORT _6*

SELECT INV_INVOICE_ID, SUM(APPLY_AMOUNT) AS AMOUNT
FROM #TEMP_MEDTOX_AR_AGING_REPORT_5
GROUP BY INV_INVOICE_ID
ORDER BY INV_INVOICE_ID

--******************START OF TEMP_MEDTOX_AR_AGING_REPORT_6 SET NOCOUNT OFF
GO

SP THREE:

CREATE PROCEDURE dbo.[Medtox_Sales_Accts_Rec_Aging_Report_8]

-- DATE INPUT PARAMETERS - END DATE
@EndDate DateTime
AS

--NUMBER OF ROWS AFFECTED BY THE T-SQL IS NOT RETRUNED
SET NOCOUNT ON

--******************VARIABLES DECLARATIONS***

--DECLARE VARIABLES TEMP_MEDTOX_AR_AGING_REPORT_7
DECLARE @MEMO_INVOICE_ID VARCHAR (15),
@APPLY_AMOUNT DECIMAL (15,2),
@APPLY_DATE DATETIME


--******************START OF TEMP_MEDTOX_AR_AGING_REPORT_7 *

--DELETE TEMP TABLE IF PREVIOUSLY CREATED
--DROP TABLE #TEMP_MEDTOX_AR_AGING_REPORT_7

-- CREATE TEMP TABLE - TEMP_MEDTOX_AR_AGING_REPORT_7
CREATE TABLE #TEMP_MEDTOX_AR_AGING_REPORT_7
(MEMO_INVOICE_ID VARCHAR(15),
APPLY_AMOUNT DECIMAL (15,2),
APPLY_DATE DATETIME)

--CLEAR TEMP TABLE IF PREVIOUSLY USED
DELETE FROM #TEMP_MEDTOX_AR_AGING_REPORT_7

--DECLARE CURSOR FOR TEMP TABLE - TEMP_MEDTOX_AR_AGING_REPORT_7
DECLARE CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_7 CURSOR

FOR SELECT MEMO_INVOICE_ID, APPLY_AMOUNT * - 1 AS APPLY_AMOUNT, apply_date
FROM dbo.RECV_MEMO_APPLY
WHERE (apply_date <= CONVERT(DATETIME, @EndDate, 102))

--OPEN CURSOR
OPEN CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_7

--FETCH FROM CUSRSOR INTO THE DECLARED VARIABLES
FETCH NEXT FROM CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_7 INTO @MEMO_INVOICE_ID, @APPLY_AMOUNT, @APPLY_DATE

--INSERT RETURNED VALUES INTO TEMP TABLE - TEMP_MEDTOX_AR_AGING_REPORT_7
INSERT INTO #TEMP_MEDTOX_AR_AGING_REPORT_7
VALUES (@MEMO_INVOICE_ID, @APPLY_AMOUNT, @APPLY_DATE)

WHILE @@FETCH_STATUS = 0

BEGIN
--FETCH FROM CUSRSOR INTO THE DECLARED VARIABLES
FETCH NEXT FROM CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_7 INTO @MEMO_INVOICE_ID, @APPLY_AMOUNT, @APPLY_DATE
--INSERT DATA INTO TEMP TABLE - TEMP_MEDTOX_AR_AGING_REPORT_7
INSERT INTO #TEMP_MEDTOX_AR_AGING_REPORT_7
VALUES(@MEMO_INVOICE_ID, @APPLY_AMOUNT, @APPLY_DATE)
END

--CLOSE CURSOR
CLOSE CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_7
--DEALLOCATE CURSOR
DEALLOCATE CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_7

--******************END OF TEMP_MEDTOX_AR_AGING_REPORT_7 ***

--******************START OF TEMP_MEDTOX_AR_AGING_REPORT _8*

SELECT MEMO_INVOICE_ID, SUM(APPLY_AMOUNT) AS APPLIED_AMT
FROM #TEMP_MEDTOX_AR_AGING_REPORT_7
GROUP BY MEMO_INVOICE_ID
ORDER BY MEMO_INVOICE_ID

--******************START OF TEMP_MEDTOX_AR_AGING_REPORT_8 *

SET NOCOUNT OFF
GO

 

[donutman]

Jay, here's my advice:
1) don't combine them
2) lighten up on the documentation
--CLOSE CURSOR
CLOSE CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_7
--DEALLOCATE CURSOR
DEALLOCATE CURSOR_TEMP_MEDTOX_AR_AGING_REPORT_7
3) Use upper and lower case
4) Eliminate the underscores...just start the next work with a capital letter
5) Don't use cursors (rewrite all 3 SPs without them)
6) Indent to make statements more readable
-Karl


[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[FJAY]

What are options if you said don't combine the SPs?
What can I use apart from cursors. Thanks.

 

[SQLSister]

donutman is right, you should NEVER use cursors for a simple update or insert. Use the set-based statments instead. Read about insert in Books online, it will tell you haw to instert using a selct statment instead of a vlaues statement. The reason why you need to replace these cursors and any others you have in your application is that they are slow, especially once you have large data tables and a multi-user environment. Designing stored procedures for users using cursors is a sure way to have your application fail in production. A process which will take minutes or even hours using a cursor will take milliseconds using a set-based statement. This is something you must learn immediately if you want your application to work in the real world.

 

[FJAY]

Good afternoon - can you please give me an example using one of my posted SPs. Thanks for your help.

 

[SQLSister]

insert into table1 (field1, field2, field3)
Select field1, field2, field3 from table2 where field4 = 'test'

This would select all the records meeting the where clause and insert them into the table you want them in using one step. A cursor would go line for line through each record and then do the inserts one at a time for each record. So if a million records are in your cursor, then a million inserts, using the syntax above: 1 insert.

 

[FJAY]

thank you. I get the logic...below is what I have for one of my SP. I guess instead of combining the SPs. create temp tables for each SPs in a single stored procedure and select from the temp tables. Will this logic work? Thank you very much.

CREATE PROCEDURE dbo.[Medtox_SalesAcctsRecAgingReport0]

@EndDate DateTime
AS

SET nocount on

--DECLARE VARIABLES #Temp_MedtoxArAgingReport1
DECLARE @Invoice_ID VARCHAR (15),
@Posting_Date DATETIME,
@Amount DECIMAL (15,2),
@Status CHAR (1)

-- CREATE TEMP TABLE - TEMP_MEDTOXARAGINGREPORT1
CREATE TABLE #Temp_MedtoxArAgingReport1
(Invoice_ID VARCHAR (15),
Posting_Date DATETIME,
Amount DECIMAL (15,2),
Status CHAR (1))

--CLEAR TEMP TABLE IF PREVIOUSLY USED
DELETE FROM #Temp_MedtoxArAgingReport1

--insert values into the #Temp_MedtoxArAgingReport1
Insert into #Temp_MedtoxArAgingReport1
--FOR
SELECT TOP 100 PERCENT dbo.CASH_RECEIPT_LINE.INVOICE_ID, dbo.CASH_RECEIPT.POSTING_DATE,
dbo.CASH_RECEIPT_LINE.AMOUNT + dbo.CASH_RECEIPT_LINE.DISCOUNT_APPLIED AS AMOUNT, dbo.CASH_RECEIPT.STATUS
FROM dbo.CASH_RECEIPT INNER JOIN
dbo.CASH_RECEIPT_LINE ON dbo.CASH_RECEIPT.CUSTOMER_ID = dbo.CASH_RECEIPT_LINE.CUSTOMER_ID AND
dbo.CASH_RECEIPT.CHECK_ID = dbo.CASH_RECEIPT_LINE.CHECK_ID
WHERE (dbo.CASH_RECEIPT.STATUS = 'A') AND (dbo.CASH_RECEIPT.POSTING_DATE <= CONVERT(DATETIME, @ENDDATE, 102)) AND
(dbo.CASH_RECEIPT_LINE.INVOICE_ID IS NOT NULL)
ORDER BY dbo.CASH_RECEIPT_LINE.INVOICE_ID

SELECT INVOICE_ID, SUM(AMOUNT) AS CHECK_AMOUNT

FROM #Temp_MedtoxArAgingReport1
GROUP BY INVOICE_ID
ORDER BY INVOICE_ID

SET NOCOUNT OFF
GO

 

[SQLSister]

You certainly can create three temp tables and use them in one SP. You can then return three different record sets or combine them in a union (although if you have the same fields in each table, you don't need to crete three tables, just write and insert statment for each of the three queries you want it based on. Looks like you are on the right track though.

There's some stuff in what you last wrote that you don't need.

--DECLARE VARIABLES #Temp_MedtoxArAgingReport1
DECLARE @Invoice_ID VARCHAR (15),
          @Posting_Date DATETIME,
          @Amount DECIMAL (15,2),
          @Status CHAR (1)

You aren't using these variables, so no need to declare them.

--CLEAR TEMP TABLE IF PREVIOUSLY USED
DELETE FROM #Temp_MedtoxArAgingReport1

You just created the temp table inthe line above, it will never need to be cleared as you haven't inserted any information. This is a local temp table, therefore if you have multiple users running the stored procedure simultaneously, the temp database will create a separate temp table for each of them.