1、当查询语句为数组变量时，不能采用IN来处理，而应采用CharIndex来处理

Declare @Nv nvarchar(100)

Set @Nv='2,18,25'

Select * From tblSell Where Charindex(','+cast(StoreId as nvarchar(7)) + ',',','+@Nv+',')

比较：

Select * From tblSell Where StoreId in (2,18,25)

2、当要把子存储过程取出来，一个方式可以使用output这是单一值，另一个方式可以使用临时表方式取得这是一组值。

方式一：

假设有存储过程
ProcA(Para1 IN NUMBER, Para2 OUT VARCHAR2);

则
DECLARE ＠XPara2 VARCHAR2(1000);
BEGIN
ProcA(100, ＠XPara2);
END

方式二：

Create Table #t
   (
 warehouseid int,
 barcode  nvarchar(20),
 NUMBER  float
   )

insert into #t exec Sp_getstocksnumber @Time = 'Jun 27 2006  16:28:27:390PM', @ShopId = 2, @WarehouseId = N'2'

Select * From #t

Drop table #t

3、要求如图：

BillID      OrderNumber         ＝＝＞tblDataVer
----------- -----------
          1           1
          1           2
          2           1 
          3           1
          3           2
          4           1
          4           2

＋

BillID      OrderNumber QtyNumber           ＝＝＞tblDataTotal
----------- ----------- -----------
          1           1          10
          1           2          10
          1           3          10
          2           1          10
          2           2          10
          3           1          10
          4           1          10
          4           2          10
          4           3          10

＝

BillID      OrderNumber QtySum     
----------- ----------- -----------
          1           1          10
          1           2          20
          2           1          20
          3           1          10
          4           1          10
          4           2          20

Select dv.BillID,dv.OrderNumber,Sum(dt.QtyNumber) QtySum From tblDataVer dv
 INNER JOIN tblDataTotal dt
  on dv.BillID = dt.BillID
  AND (dv.OrderNumber = dt.OrderNumber
  Or ((Select Max(dv2.OrderNumber) From tblDataVer dv2 WHERE dv2.BillID = dv.BillID) = dv.OrderNumber
   AND dv.OrderNumber < dt.OrderNumber))
Group By dv.BillID,dv.OrderNumber
Order By dv.BillID

4、表如下：

BillID      PlanID        QtyNumber   ==>tblPlanNumber
----------- ----------- -----------
          1           1          10
          1           2          10
          1           3          10
          2           4          10
          2           5          10
          3           6          10
          4           7          10
          4           8          10
          4           9          10

要求：相同的BillID，按PlanID来重新指定ID传为OrderNumber,如下：
BillID      OrderNumber QtyNumber      
----------- ----------- -----------
          1           1          10
          1           2          10
          1           3          10
          2           1          10
          2           2          10
          3           1          10
          4           1          10
          4           2          10
          4           3          10
方法：
SELECT BillID,pn.PlanID,
 (SELECT COUNT(pn2.PlanID)
   FROM tblPlanNumber pn2
 Where pn2.BillID = pn.BillID And pn2.PlanID <= pn.PlanID),
 QtyNumber From tblPlanNumber pn

跨库查询

SELECT a.*
FROM OPENROWSET('MSDASQL',
   'DRIVER={SQL Server};SERVER=vas;UID=sa;PWD=sa',
   'Select * from NorthWind.dbo.tblEmployee') AS a