declare @sql varchar(4000)
set @sql='select splb,spm'
select @sql=@sql+' ,sum(case when chr='''+chr+''' then sl else 0 end) '''+chr+''''
from #temp
group by chrselect @sql=@sql+' from #temp group by splb,spm,chr order by 1 desc,3 desc'exec (@sql)
如上 这段 代码在Delphi中怎么执行???
set @sql='select splb,spm'
select @sql=@sql+' ,sum(case when chr='''+chr+''' then sl else 0 end) '''+chr+''''
from #temp
group by chrselect @sql=@sql+' from #temp group by splb,spm,chr order by 1 desc,3 desc'exec (@sql)
如上 这段 代码在Delphi中怎么执行???
用Query控件或者DataSet控件直接调用就行了!
set @sql='select splb,spm'
select @sql=@sql+' ,sum(case when chr='''+chr+''' then sl else 0 end) '''+chr+''''
from #temp
group by chr
select @sql=@sql+' from #temp group by splb,spm,chr order by 1 desc,3 desc'
exec (@sql)調用:
adoquery.active:=false;
adoquery.active:=true;
那我如果 要在where条件 带参数 该怎么写啊 我写 老是在 : 附近报错
GO
/****** Object: StoredProcedure [dbo].[SP_HR_DAY_AUDIT] Script Date: 08/12/2010 19:41:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO-- =============================================
/*
作者:天涯浪子一居士(有志者事竟成破釜沉舟百二秦关终属楚;苦心人天不负卧薪尝胆三千越甲可吞吴!)
时间:2010-08-09
地点:广东東莞
功能:產生公司eHr人事歷史稽核報表數據
*/
-- =============================================ALTER PROCEDURE [dbo].[SP_HR_DAY_AUDIT]
@rq smalldatetime,
@sWhere nvarchar(10)
AS
BEGIN
declare @ts int
select @ts=Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@rq)+1, 0)))
declare @i1 int select @i1=1 declare @xx1 nvarchar(2)
delete from HR_DAY_AUDIT
while @i1<=@ts
begin
declare @kk1 int select @kk1=0 declare @kkk1 int select @kkk1=0
if @i1<=9 select @xx1='0'+CAST(@i1 as nvarchar(1))
else select @xx1=CAST(@i1 as nvarchar(2))
select @kk1=(select datepart(weekday, CONVERT(smalldatetime,CONVERT(nvarchar(8),@rq,120) +@xx1) ) - 1 )
if exists( select * from HR_HOLIDAYS where COLRQ=CONVERT(nvarchar(8),@rq,120) +@xx1 )
select @kkk1=1
INSERT INTO HR_DAY_AUDIT(COLRQ,COLCDN) VALUES( CONVERT(nvarchar(8),@rq,120) +@xx1,'>'+@sWhere)
select @i1=@i1+1
end
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_HA1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_HA1] --臨時表1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_HA2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_HA2] --臨時表2
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HR_AUDI_DAY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HR_AUDI_DAY] --日稽核表
SELECT A.COLAID,A.COLRQ,A.SL,B.BM INTO _HA1 FROM
(
SELECT [COLAID],[COLRQ],SUM(COLJBT) AS SL
FROM [MES].[dbo].[HR_EMPLOYEE_DAY]
WHERE COLRQ>=CONVERT(nvarchar(8),@rq,120)+'01' AND
COLRQ<=CONVERT(nvarchar(8),@rq,120)+CONVERT(NVARCHAR(2),@ts)
GROUP BY [COLAID],[COLRQ]
) A LEFT JOIN Res_Hrd_Temp B ON A.COLAID=B.Account_Id
WHERE SL>CAST(@sWhere as float)
SELECT COLRQ,BM,COUNT(*) AS SL INTO _HA2 FROM _HA1 GROUP BY COLRQ,BM
declare @sql1_1 varchar(8000)
set @sql1_1 = 'select BM,'
select @sql1_1 = @sql1_1 + 'sum(case COLRQ when '''+COLRQ+'''then SL else 0 end) as '''+'THR'+RIGHT(COLRQ,2)+''','
from (select COLRQ from HR_DAY_AUDIT ) as a
select @sql1_1 = left(@sql1_1,len(@sql1_1)-1) + ' INTO HR_AUDI_DAY from _HA2 group by BM order by BM'
exec(@sql1_1)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_HA1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_HA1]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_HA2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_HA2]
--SELECT * FROM HR_AUDI_DAY
ENDGO