存储过程创建临时表和返回临时表

  工作需要将两条复杂的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