存储过程权限和订单类别

USE [BooksBrothersWebOrder]
GO
/****** Object: StoredProcedure [dbo].[sp_GetUserRightsByCustID] Script Date: 09/19/2012 14:46:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 

/*
Name: dbo.sp_GetUserRightsByCustID
Description: --取得指定Cust的相关权限
Parameters:
@CustID VARCHAR(10)
Return Values:
Ref. Tables:
R dbo.O_OrderTypeMaster
R dbo.A_UserRights

-------------------------------------------------------------------------------------------------
Author Name: jokey.chen
Creation Date: 2012-03-23
Modification Log:
Example:
EXEC sp_GetUserRightsByCustID '1',''
EXEC sp_GetUserRightsByCustID '1','Size'

*/

ALTER PROCEDURE [dbo].[sp_GetUserRightsByCustID]
(
@CustID VARCHAR(10),
@OrderType VARCHAR(10)


)
AS

IF @OrderType=''
SELECT CustID=ISNULL(A.CustID,@CustID),O.OrderType,O.[Description],
AWConfirm=CASE WHEN O.AWConfirm=0 THEN -1 ELSE ISNULL(A.AWConfirm,0) END,
OrderConfirm=CASE WHEN O.OrderConfirm=0 THEN -1 ELSE ISNULL(A.OrderConfirm,0) END
FROM dbo.O_OrderTypeMaster O
LEFT JOIN dbo.A_UserRights A ON A.OrderType=O.OrderType AND A.CustID=@CustID
WHERE O.Activate=1
--AND (CASE WHEN O.AWConfirm=0 THEN -1 ELSE ISNULL(A.AWConfirm,0) END>-1 OR CASE WHEN O.OrderConfirm=0 THEN -1 ELSE ISNULL(A.OrderConfirm,0) END>-1)
ELSE
SELECT CustID=ISNULL(A.CustID,@CustID),O.OrderType,O.[Description],
AWConfirm=CASE WHEN O.AWConfirm=0 THEN -1 ELSE ISNULL(A.AWConfirm,0) END,
OrderConfirm=CASE WHEN O.OrderConfirm=0 THEN -1 ELSE ISNULL(A.OrderConfirm,0) END
FROM dbo.O_OrderTypeMaster O
LEFT JOIN dbo.A_UserRights A ON A.OrderType=O.OrderType AND A.CustID=@CustID
WHERE O.Activate=1 AND O.OrderType=@OrderType
--AND (CASE WHEN O.AWConfirm=0 THEN -1 ELSE ISNULL(A.AWConfirm,0) END>-1 OR CASE WHEN O.OrderConfirm=0 THEN -1 ELSE ISNULL(A.OrderConfirm,0) END>-1)