--动态sql语句用法
--1 :普通SQL语句可以用Exec执行 --eg:
Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N --2:字段名,表名,数据库名之类作为变量时,必须用动态SQL --eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 --当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 --3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls) --如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
--1 :普通SQL语句可以用Exec执行 --eg:
Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N --2:字段名,表名,数据库名之类作为变量时,必须用动态SQL --eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 --当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 --3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls) --如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
還是沒有解決
還是那樣 1 字符連接成功 ,執行出錯
2 或者連接不成功
正好就是表變量才出錯,要是普通變量就不會出錯了
CREATE PROCEDURE [dbo].[sp_ConsStoreAge](@StartDate char(10),@EndDate char(10),@strWhere varchar(8000))
AS
SET NOCOUNT ON
----------------------@tmpStore[Consignment的庫存]----------------------------------
Declare @tmpStore TABLE(ItemID int,VendID int,CustID int,CustItemCode varchar(30),OsQty numeric(30,6),Price numeric(20,6),Rate numeric(20,6))
insert into @tmpStore
SELECT ItemID,A.VendID,A.CustID,CustItemCode,sum(OsQty) OsQty,Price,B.SoRate Rate
FROM (SELECT InvoiceID, D.ItemID, D.CustItemCode,(A.InvQty-isnull(B.OutQty, 0)) OsQty,Case when NewPrice=0 then D.Price else NewPrice end as Price,G.CustID,G.VendID FROM T_ConsInvoice_D A
INNER JOIN T_ConsInvoice G ON a.InvoiceID = G.ID
INNER JOIN T_Consignment_D D ON a.Cons_D_ID = D.ID
LEFT OUTER JOIN (SELECT A.Inv_D_ID, SUM(A.OutQty) OutQty FROM T_Outlist_D_D A INNER JOIN
T_OutList_D B ON A.Out_D_ID = B.ID INNER JOIN T_OutList C ON B.OutListID = C.ID WHERE (C.Status <> 'X')
GROUP BY a.Inv_D_ID) B ON a.ID =B.Inv_D_ID
LEFT OUTER JOIN (SELECT a.Cons_D_ID, SUM(a.InvQty) AS InvQty
FROM dbo.T_ConsInvoice_D a INNER JOIN dbo.T_ConsInvoice B ON A.InvoiceID = B.ID WHERE (B.Status <> 'X') GROUP BY A.Cons_D_ID) C
ON a.Cons_D_ID = C.Cons_D_ID) A
INNER JOIN T_ConsInvoice B ON A.InvoiceID = B.ID
where OsQty>0 AND B.TRANSFERAR=0 AND STATUS<>'X' and InvoiceDate between '' and @EndDate Group By ItemID,A.VendID,A.CustID,CustItemCode,Price,B.SoRate
--------------------@tmpAge[範圍内庫齡]-------------------
Declare @tmpAge TABLE(ItemID int,VendID int,CustID int,CustItemCode varchar(30),OsQty numeric(30,6),Age int)
insert into @tmpAge
SELECT ItemID,A.VendID,A.CustID,CustItemCode,OsQty,Datediff(day,InvoiceDate,@EndDate) Age FROM (SELECT InvoiceID, D.ItemID, D.CustItemCode,(A.InvQty-isnull(B.OutQty, 0)) OsQty,
Case when NewPrice=0 then D.Price else NewPrice end as Price,G.CustID,G.VendID FROM T_ConsInvoice_D A
INNER JOIN T_ConsInvoice G ON a.InvoiceID = G.ID
INNER JOIN T_Consignment_D D ON a.Cons_D_ID = D.ID
LEFT OUTER JOIN (SELECT A.Inv_D_ID, SUM(A.OutQty) OutQty FROM T_Outlist_D_D A INNER JOIN
T_OutList_D B ON A.Out_D_ID = B.ID INNER JOIN T_OutList C ON B.OutListID = C.ID WHERE (C.Status <> 'X')
GROUP BY a.Inv_D_ID) B ON a.ID =B.Inv_D_ID
LEFT OUTER JOIN (SELECT a.Cons_D_ID, SUM(a.InvQty) AS InvQty
FROM dbo.T_ConsInvoice_D a INNER JOIN dbo.T_ConsInvoice B ON A.InvoiceID = B.ID WHERE (B.Status <> 'X') GROUP BY A.Cons_D_ID) C
ON a.Cons_D_ID = C.Cons_D_ID) A
INNER JOIN T_ConsInvoice B ON A.InvoiceID = B.ID
where OsQty>0 AND B.TRANSFERAR=0 AND STATUS<>'X' and InvoiceDate between @StartDate and @EndDate
------------------------------補充不足的庫存-------------------------------
insert into @tmpAge select A.ItemID,A.VendID,A.CustID,A.CustItemCode,(A.OsQty- isnull(B.OsQty,0)) OsQty,361 Age
from @tmpStore A
left join (select ItemID,VendID,CustID,CustItemCode,sum(OsQty) OsQty from @tmpAge group by ItemID,VendID,CustID,CustItemCode ) B
on A.ItemID=B.ItemID and A.VendID=B.VendID and A.CustID=B.CustID and A.CustItemCode=B.CustItemCode
where isnull(B.OsQty,0)<A.OsQty
----------------------------------組織庫存對應的庫齡------------------------------------------------------
Declare @strSqlHead varchar(8000)
select @strSqlHead='select LineNick,LineName,FamilyNick,FamilyName,ItemCode,VendNick,VendName,CustNick,CustName,A.CustItemCode,A.OsQty,A.Price,A.Rate,sum(OsQty1) OsQty1,sum(OsQty2) OsQty2,sum(OsQty3) OsQty3,sum(OsQty4) OsQty4,sum(OsQty5) OsQty5
,sum(OsQty6) OsQty6,sum(OsQty7) OsQty7,sum(OsQty8) OsQty8,sum(OsQty9) OsQty9,sum(OsQty10) OsQty10,sum(OsQty11) OsQty11,sum(OsQty12) OsQty12,sum(OsQty13) OsQty13,
sum(round(round(OsQty1*Price,2)*rate,2)) OsMoney1,sum(round(round(OsQty2*Price,2)*rate,2)) OsMoney2,sum(round(round(OsQty3*Price,2)*rate,2)) OsMoney3,sum(round(round(OsQty4*Price,2)*rate,2)) OsMoney4,
sum(round(round(OsQty5*Price,2)*rate,2)) OsMoney5,sum(round(round(OsQty6*Price,2)*rate,2)) OsMoney6,sum(round(round(OsQty7*Price,2)*rate,2)) OsMoney7,
sum(round(round(OsQty8*Price,2)*rate,2)) OsMoney8,sum(round(round(OsQty9*Price,2)*rate,2)) OsMoney9,sum(round(round(OsQty10*Price,2)*rate,2)) OsMoney10,
sum(round(round(OsQty11*Price,2)*rate,2)) OsMoney11,sum(round(round(OsQty12*Price,2)*rate,2)) OsMoney12,sum(round(round(OsQty13*Price,2)*rate,2)) OsMoney13
from ' +@tmpStore + ' A inner join (select ItemID,A.VendID,A.CustID,CustItemCode,
case when 0<Age and Age<=30 then sum(OsQty) else 0 end OsQty1,
case when 30<Age and Age<=60 then sum(OsQty) else 0 end as OsQty2,
case when 60<Age and Age<=90 then sum(OsQty) else 0 end as OsQty3 ,
case when 90<Age and Age<=120 then sum(OsQty) else 0 end as OsQty4,
case when 120<Age and Age<=150 then sum(OsQty) else 0 end as OsQty5,
case when 150<Age and Age<=180 then sum(OsQty) else 0 end as OsQty6,
case when 180<Age and Age<=210 then sum(OsQty) else 0 end as OsQty7 ,
case when 210<Age and Age<=240 then sum(OsQty) else 0 end as OsQty8 ,
case when 240<Age and Age<=270 then sum(OsQty) else 0 end as OsQty9 ,
case when 270<Age and Age<=300 then sum(OsQty) else 0 end as OsQty10,
case when 300<Age and Age<=330 then sum(OsQty) else 0 end as OsQty11,
case when 330<Age and Age<=360 then sum(OsQty) else 0 end as OsQty12,
case when 360<Age then sum(OsQty) else 0 end as OsQty13 from ' +@tmpAge+ ' A Group By ItemID,A.VendID,A.CustID,CustItemCode,Age) B
on A.ItemID=B.ItemID and A.VendID=B.VendID and A.CustID=B.CustID and A.CustItemCode=B.CustItemCode
left join T_Item C on A.ItemID=C.ID
left join T_Family D on C.FamilyCode=D.FamilyCode
left join T_ProductLine E on D.LineCode=E.LineCode
left join T_Vendor F on A.VendID=F.ID
left join T_Customer G on A.CustID=G.ID ' select @strSqlHead=@strSqlHead+@strWhere+' Group By LineNick,LineName,FamilyNick,FamilyName,ItemCode,VendNick,VendName,CustNick,CustName,A.CustItemCode,A.OsQty,A.Price,A.Rate' exec(@strSqlHead)
GO
本來,普通的變量我們就是這樣連的,可是這樣連
出現 "Error 127 Must Declare the Variable '@tmpStore' "
select * from @tmpStore
我的目的是把結果結果 strWhere篩選了再輸出(若不篩選則輸出就可以)
----------------------- -----------------------------------------
而我現在必須得把 表变量 内嵌
失望了......... >_<~~~~~~~~~~
http://community.csdn.net/Expert/topic/4441/4441671.xml?temp=.2928583