DECLARE @Formula = '(Unit3 / (QtyPerUnit / 1000) /12)'
DECLARE @TempTbl table (
MatID varchar(15),Unit3 float,QtyPerUnit Float --Result As @Formula
)
insert into @TempTbl
select S.MatID, sum(S.Unit3) as Unit3,M.QtyPerUnit from tbStock S
inner join tbMat M on S.MatID = M.MatID
inner join tbSubCategory SC on M.SubCatID = sc.SubCatID
where sc.SubCatID = 'SCID002'
group by S.MatID,M.QtyPerUnit
select *,QtyPerUnit * Unit3 As Result from @TempTbl
ได้แล้วครับ
1. สร้าง Procedure แทน Function Code (SQL)
CREATE PROCEDURE GetDozen
-- Add the parameters for the stored procedure here
@SubCatID varchar(max),
@Formula varchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
drop table ##Temps
drop table ##TempTable
select * into ##Temps
from
(select S.MatID, sum(S.Unit3) as Unit3,M.QtyPerUnit from tbStock S
inner join tbMat M on S.MatID = M.MatID
inner join tbSubCategory SC on M.SubCatID = sc.SubCatID
where sc.SubCatID = @SubCatID
group by S.MatID,M.QtyPerUnit) data
exec ('SELECT '+ @Formula +' As RES,MatID INTO ##TempTable FROM ##Temps')
select * from ##TempTable
END
GO
Load balance : Server 02
