sql - 将参数值传递给表？

Question

我有以下存储过程查询，现在我需要将一些参数值传递给 SQL Server 中此查询中的“详细信息”表列，如“cus_name”和“cus_tel”。

SELECT APO_Order_Id AS              Purchase_Order_Number,
       p.ref_code AS                Project_ID,
       p.PJM_UserDefinedProjID AS   Project_Code,
       CASE m.APO_Use_Alt_Address
           WHEN 0
           THEN LTRIM(isnull(p.PJM_LotNumber, '')+' '+isnull(p.PJM_StreetNumber, '')+' '+isnull(p.PJM_SiteAddr1, '')+' '+isnull(p.PJM_Suburb, '')+' '+isnull(p.PJM_PostCode, ''))
           WHEN 1
           THEN LTRIM(isnull(p.PJM_Alt_LotNumber, '')+' '+isnull(p.PJM_Alt_StreetNumber, '')+' '+isnull(p.PJM_Alt_SiteAddr1, '')+' '+isnull(p.PJM_Alt_Suburb, '')+' '+isnull(p.PJM_Alt_PostCode, ''))
       END AS                       Project_Site_Address,
       p.PJM_StartDate AS           Site_StartDate,
       s.CNT_ClientName AS          Creditor,
       m.APO_Description AS         Purchase_Order_Description,
       isnull(d.Order_Amount, 0) AS Order_Total,
       ui.tui_username AS           Project_Manager
FROM Account_APOrderMaster AS m
     LEFT JOIN
(
    SELECT APOD_Master_Id,
           APOD_Project_Id,
           SUM(APOD_Total_Amount) AS Order_Amount
    FROM Account_APOrderDetail
    GROUP BY APOD_Master_Id,
             APOD_Project_Id
) AS d ON m.Ref_Code = d.APOD_Master_Id
     LEFT JOIN Client_Name AS s ON m.APO_Supplier_ID = s.Ref_Code
     LEFT JOIN Project_Master AS p ON d.APOD_Project_Id = p.ref_code
     LEFT JOIN System_UserInformation AS ui ON m.APO_ProjectManagerId = ui.Ref_Code
WHERE apo_order_id = @purchase_order_number
GO

我怎样才能做到这一点？

完成的存储过程代码

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spGetPurchaseOrderProjectDetails]
@purchase_order_number as varchar(20)
AS
SET NOCOUNT ON


SELECT APO_Order_Id AS              Purchase_Order_Number,
       p.ref_code AS                Project_ID,
       p.PJM_UserDefinedProjID AS   Project_Code,
       CASE m.APO_Use_Alt_Address
           WHEN 0
           THEN LTRIM(isnull(p.PJM_LotNumber, '')+' '+isnull(p.PJM_StreetNumber, '')+' '+isnull(p.PJM_SiteAddr1, '')+' '+isnull(p.PJM_Suburb, '')+' '+isnull(p.PJM_PostCode, ''))
           WHEN 1
           THEN LTRIM(isnull(p.PJM_Alt_LotNumber, '')+' '+isnull(p.PJM_Alt_StreetNumber, '')+' '+isnull(p.PJM_Alt_SiteAddr1, '')+' '+isnull(p.PJM_Alt_Suburb, '')+' '+isnull(p.PJM_Alt_PostCode, ''))
       END AS                       Project_Site_Address,
       p.PJM_StartDate AS           Site_StartDate,
       s.CNT_ClientName AS          Creditor,
       m.APO_Description AS         Purchase_Order_Description,
       isnull(d.Order_Amount, 0) AS Order_Total,
       ui.tui_username AS           Project_Manager
FROM Account_APOrderMaster AS m
     LEFT JOIN
(
    SELECT APOD_Master_Id,
           APOD_Project_Id,
           SUM(APOD_Total_Amount) AS Order_Amount
    FROM Account_APOrderDetail
    GROUP BY APOD_Master_Id,
             APOD_Project_Id
) AS d ON m.Ref_Code = d.APOD_Master_Id
     LEFT JOIN Client_Name AS s ON m.APO_Supplier_ID = s.Ref_Code
     LEFT JOIN Project_Master AS p ON d.APOD_Project_Id = p.ref_code
     LEFT JOIN System_UserInformation AS ui ON m.APO_ProjectManagerId = ui.Ref_Code
WHERE apo_order_id = @purchase_order_number
GO

标签： sqlsql-server