存储过程中的SQL语句

[w362435819]

USE [Warehouse]
GO
/****** Object:  StoredProcedure [dbo].[SP_T_Operation_SelectAll]    Script Date: 07/19/2012 14:21:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------------------------------
--用途：模糊查询 
--项目名称：
--说明：
--时间：2012-7-17 15:00:36
------------------------------------
ALTER PROCEDURE [dbo].[SP_T_Operation_SelectAll_InOut]
(
	@SheetID varchar(20),
    @date1 varchar(25),
    @date2 varchar(25),
    @SheetOwner varchar(10),
    @CostCenterCode varchar(10),
    @ProductName nvarchar(100)
)
AS
BEGIN
	DECLARE @sql nvarchar(MAX)
	SET @sql ='
			select 
			a.SheetID,a.OperationDate,a.OriginalSheetID,			
			c.ProductName,a.ReturnReason,
			case
			when a.OriginalSheetID="" or a.OriginalSheetID is NULL then b.Quantity
			end as outs,
			case
			when a.OriginalSheetID<>"" or a.OriginalSheetID is not NULL then b.Quantity
			end as ins,
			b.UnitPrice,
			d.UserName,b.CostCenterCode
			from T_Operation a left join 
            T_OperationDetail b on a.SheetID=b.SheetID 
            left join T_Product c on b.ProductID=c.ProductID
            left join T_User d on a. SheetOwner=d.UserID
            where 1=1
			'			
	IF @SheetType IS NOT NULL AND @SheetType <>''
		SET @sql=@sql+' and A.SheetType ='+@SheetType;
		
	IF @date1 IS NOT NULL AND @date1<>'' and @date2 IS NOT NULL AND @date2<>''
		SET @sql=@sql+' and A.OperationDate >='''+ CONVERT(varchar(100), @date1, 120)+''' and A.OperationDate <='''+CONVERT(varchar(100), @date2, 120)+'''';
		
	IF @SheetOwner IS NOT NULL AND @SheetOwner<>''
		SET @sql=@sql+' and d.UserName LIKE(''%'+@SheetOwner+'%'')';
		
	IF @CostCenterCode IS NOT NULL AND @CostCenterCode<>''
		SET @sql=@sql+' and B.CostCenterCode LIKE(''%'+@CostCenterCode+'%'')';
		
	IF @ProductName IS NOT NULL AND @ProductName<>''
		SET @sql=@sql+' and C.ProductName LIKE(''%'+@ProductName+'%'')';
	
	PRINT (@sql)
	
	EXEC (@sql)
	
END

主要是这一段

case
when a.OriginalSheetID="" or a.OriginalSheetID is NULL then b.Quantity
end as outs,
case
when a.OriginalSheetID<>"" or a.OriginalSheetID is not NULL then b.Quantity
end as ins,

想实现当OriginalSheetID为空，则将Quantity当做OUTS输出
当OriginalSheetID不为空，则将Quantity当做ins输出
应该怎么写呢？

[w362435819]

现在等，急，为很么每次我提问都没有人来回答，伤心

[百年树人]

现在输出的结果是什么，要改成什么样的？

[SQL77]

SET @sql ='
select
a.SheetID,a.OperationDate,a.OriginalSheetID,
c.ProductName,a.ReturnReason,
case
when a.OriginalSheetID='''' or a.OriginalSheetID is NULL then b.Quantity
end as outs,
case
when a.OriginalSheetID<>'''' or a.OriginalSheetID is not NULL then b.Quantity
end as ins,
b.UnitPrice,
d.UserName,b.CostCenterCode
from T_Operation a left join
T_OperationDetail b on a.SheetID=b.SheetID
left join T_Product c on b.ProductID=c.ProductID
left join T_User d on a. SheetOwner=d.UserID
where 1=1
'

[w362435819]

[Quote=引用 3 楼 的回复:]
SET @sql ='
select
a.SheetID,a.OperationDate,a.OriginalSheetID,
c.ProductName,a.ReturnReason,
case
when a.OriginalSheetID='''' or a.OriginalSheetID is NULL then b.Quantity
end as ……
[/Quote]
人才，你的回答是对的，我忘了SQL里引号是单引号了，我无语