Geek Answers Handbook

How to reuse code in SQL stored procedures?

We use SQL Server 2005. All data access is through stored procedures. Our stored selection procedures always return multiple result sets.

For example:

CREATE PROCEDURE hd_invoice_select(@id INT) AS
    SELECT * FROM Invoice WHERE InvoiceID = @id
    SELECT * FROM InvoiceItem WHERE InvoiceID = @id
    SELECT * FROM InvoiceComments WHERE InvoiceID = @id
    RETURN

Our level of access to application data creates a graph of objects based on the results (O / R map style).

The problem is that we have many different options for choosing an invoice. They all return the same structure, only for different selection criteria. For example, I also have:

CREATE PROCEDURE hd_invoice_selectAllForCustomer(@customerID INT) AS
    SELECT * FROM Invoice WHERE CustomerID = @customerID
    SELECT * FROM InvoiceItem WHERE InvoiceID IN 
        (SELECT InvoiceID FROM Invoice WHERE CustomerID = @customerID)
    SELECT * FROM InvoiceComments WHERE InvoiceID = @id
        (SELECT InvoiceID FROM Invoice WHERE CustomerID = @customerID)
    RETURN

and I have many others, including:

hd_invoice_selectActive()
hd_invoice_selectOverdue()
hd_invoice_selectForMonth(@year INT, @month INT)

and I have the same model for many concepts (Clients, Employees, etc.)

, . "" , procs, .

, : ?

, . . , , .

+5
10

, . SQL Server 2008:

CREATE TYPE InvoiceListTableType AS TABLE 
(
    InvoiceId INT
);
GO

CREATE PROCEDURE hd_invoice_selectFromTempTable
(
    @InvoiceList InvoiceListTableType READONLY
)
AS
BEGIN
    SELECT * FROM Invoice WHERE InvoiceID IN
        (SELECT InvoiceId FROM @InvoiceList)

    SELECT * FROM InvoiceItem WHERE InvoiceID IN 
        (SELECT InvoiceId FROM @InvoiceList)

    SELECT * FROM InvoiceComments WHERE InvoiceID IN
        (SELECT InvoiceId FROM @InvoiceList)

    RETURN
END
GO

CREATE PROCEDURE hd_invoice_select(@id INT) AS
BEGIN
    DECLARE @InvoiceList AS InvoiceListTableType;

    SELECT id AS ID 
        INTO @InvoiceList

    EXEC hd_invoice_selectFromTempTable(@InvoiceList)
    RETURN
END
GO

CREATE PROCEDURE hd_invoice_selectAllForCustomer(@customerID INT) AS
BEGIN
    DECLARE @InvoiceList AS InvoiceListTableType;

    SELECT invoiceID as ID
        INTO @InvoiceList
        FROM Invoice WHERE CustomerID = @customerID

    EXEC hd_invoice_selectFromTempTable(@InvoiceList)
    RETURN
END
GO

CREATE PROCEDURE hd_invoice_selectAllActive AS
BEGIN
    DECLARE @InvoiceList AS InvoiceListTableType;

    SELECT invoiceID as ID
        INTO @InvoiceList
        FROM Invoice WHERE Status = 10002

    EXEC hd_invoice_selectFromTempTable(@InvoiceList)
    RETURN
END
GO
+1

"" . - .

+2

? proc Invoice, .

CREATE PROCEDURE hd_invoice_select
(
    @id INT = NULL
    , @customerId INT = NULL
) AS
BEGIN
    SELECT * 
        FROM Invoice 
        WHERE 
            (
                @id IS NULL
                OR InvoiceID = @id
            )
            AND (
                @customerId IS NULL
                OR CustomerID = @customerId
            )
    RETURN
END

proc , @id @customerId NULL, InvoiceID @id @customerId NULL ( ) @customerId @id NULL .

, . procs, procs, . / , .

+1

, . , , , . , .

. , , .

storec, :

CREATE PROCEDURE hd_invoice_selectFromTempTable AS

    /* Get the IDs from an existing #TempInvoiceIDs temporary table */

    SELECT * FROM Invoice WHERE InvoiceID IN
        (SELECT ID FROM #TempInvoiceIDs)

    SELECT * FROM InvoiceItem WHERE InvoiceID IN 
        (SELECT ID FROM #TempInvoiceIDs)

    SELECT * FROM InvoiceComments WHERE InvoiceID IN
        (SELECT ID FROM #TempInvoiceIDs)

    RETURN

, :

CREATE PROCEDURE hd_invoice_select(@id INT) AS

    /* Fill #TempInvoiceIDs with matching IDs */
    SELECT id AS ID INTO #TempInvoiceIDs

    EXEC hd_invoice_selectFromTempTable
    RETURN

CREATE PROCEDURE hd_invoice_selectAllForCustomer(@customerID INT) AS

    /* Fill #TempInvoiceIDs with matching IDs */
    SELECT invoiceID as ID
    INTO #TempInvoiceIDs
    FROM Invoice WHERE CustomerID = @customerID

    EXEC hd_invoice_selectFromTempTable
    RETURN

CREATE PROCEDURE hd_invoice_selectAllActive AS

    /* Fill #TempInvoiceIDs with matching IDs */
    SELECT invoiceID as ID
    INTO #TempInvoiceIDs
    FROM Invoice WHERE Status = 10002

    EXEC hd_invoice_selectFromTempTable
    RETURN

? , BLOB.

+1

.

.

0

, CRUD. procs SQL.

, , - IN EXISTS SQL.

0

:

InvoiceID. .

2005 , BLOB SQL Server, : SQL Server 2005

- ( ) ( ).

0

, temp table, , .

, "", "", , , .

, .

0

VIEWS "". , , , .....

0

Perhaps you should learn how to use unions. You can put the basic join of the three tables in the view and simply query what when passing sp various parameters. In addition, you should not use select * ever at all in production code. Return only a few columns that you really need, and your system will work better. In addition, you will not have unforeseen results when people change the structure for you.

0
source

More articles:


All Articles