存儲過程建立臨時表和傳回臨時表

  工作需要将兩條複雜的SQL的結果集放到一datatable中，本來是單獨寫的SQL語句，但是得到結果集用DataTable排序之後感覺排序的結果和SQL排序的結果不對，很糾結寫了這個存儲過程。

SET ansi_nulls ON

SET quoted_identifier ON

GO

ALTER PROC [dbo].[orderdataOtherorder]

@storeid INT,

@customerid INT,

@slipnumber VARCHAR(30),

@year INT,

@month INT

AS

-- 建立臨時表

DECLARE

@order_id INT,

@store_id INT,

@store_name NVARCHAR(15),

@customer_id INT,

@abbreviation VARCHAR(30),

@slip_number INT,

@order_date datetime,

@ship_date datetime,

@quantity_sum INT,

@cost_sum INT,

@price_sum INT,

@ship_quantity INT,

@inspect_quantity INT,

@appoint_date datetime,

@payment_date datetime

IF object_id('ch_db.dbo.#tempOrderdataOtherorder') IS NOT NULL

BEGIN

DROP TABLE #temporderdataotherorder --删除臨時表

END

-- 不傳回計數

SET nocount ON

-- 建立臨時表

CREATE TABLE #temporderdataotherorder (

order_id INT,

store_id INT,

store_name NVARCHAR(15),

customer_id INT,

abbreviation VARCHAR(30),

slip_number INT,

order_date datetime,

ship_date datetime,

quantity_sum INT,

cost_sum INT,

price_sum INT,

ship_quantity INT,

inspect_quantity INT,

appoint_date datetime,

payment_date datetime)

-- 将得到的資料插入到臨時表中

INSERT INTO #temporderdataotherorder

SELECT orderdata.order_id,

orderdata.store_id,

STORE.store_name,

orderdata.customer_id,

customer.abbreviation,

Right(orderdata.slip_number,6) AS slip_number,

orderdata.order_date,

Coalesce(orderdata.delivery_date,orderdata.ship_date,

orderdata.print_date) AS ship_date,

Sum(Coalesce(orderdetail.inspect_quantity,orderdetail.ship_quantity,

orderdetail.order_quantity)) AS quantity_sum,

Sum(orderdetail.exclusive_cost * Coalesce(orderdetail.inspect_quantity,orderdetail.ship_quantity,

orderdetail.order_quantity)) AS cost_sum,

Sum(orderdetail.exclusive_price * Coalesce(orderdetail.inspect_quantity,orderdetail.ship_quantity,

orderdetail.order_quantity)) AS price_sum,

(SELECT Count(detail_id) AS expr1

FROM orderdetail

WHERE (order_id = orderdata.order_id)

AND (ship_quantity <> order_quantity)) AS ship_quantity,

(SELECT Count(detail_id) AS expr1

FROM orderdetail

WHERE (order_id = orderdata.order_id)

AND (inspect_quantity <> order_quantity)) AS inspect_quantity,

orderdata.appoint_date,

orderdata.payment_date

FROM orderdata

INNER JOIN orderdetail

ON orderdata.order_id = orderdetail.order_id

INNER JOIN STORE

ON orderdata.store_id = STORE.store_id

INNER JOIN customer

ON orderdata.customer_id = customer.customer_id

WHERE (@storeid = 0

OR orderdata.store_id = @storeid)

AND (@customerid = 0

OR orderdata.customer_id = @customerid)

AND (@slipnumber = ''

OR Right(orderdata.slip_number,6) = @slipnumber)

AND (Year(orderdata.order_date) = @year)

AND (Month(orderdata.order_date) = @month)

AND (orderdata.arrival_date IS NOT NULL )

GROUP BY orderdata.order_id,

orderdata.store_id,

orderdata.customer_id,

orderdata.slip_number,

orderdata.order_date,

STORE.store_name,

customer.abbreviation,

Coalesce(orderdata.delivery_date,orderdata.ship_date,

orderdata.print_date),

orderdata.appoint_date,

orderdata.payment_date

ORDER BY orderdata.order_date DESC

SELECT order_id,

store_id,

store_name,

customer_id,

abbreviation,

slip_number,

order_date,

ship_date,

quantity_sum,

cost_sum,

price_sum,

ship_quantity,

inspect_quantity,

appoint_date,

payment_date

FROM #temporderdataotherorder

SET nocount off

GO