这是我存储过程的代码:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[p_getData]
@name varchar(20),
@field varchar(1000),
@startdate varchar(20),
@enddate varchar(20)
AS
declare @sql varchar(max),
@starttime datetime,
@endtime datetime,
@startyear int,
@endyear int,
@curyear varchar(20)
BEGIN
set @startyear=1950
set @endyear=2200
if(@field='')
set @field='*'
if(@startdate='')
set @startdate=@enddate
if(@enddate='')
set @enddate=@startdate
if(len(@startdate)=4)
set @startdate=@startdate+'0101'
if(len(@enddate)=4)
set @enddate=@enddate+'1231'
if(len(@startdate)=6)
set @startdate=@startdate+'01'
if(len(@enddate)=6 and isnumeric(@enddate)=1)
set @endtime=dateadd(dd,-1,dateadd(mm,1,cast(@enddate+'01' as datetime)))
if(isdate(@startdate)>0)
begin
set @starttime = cast(@startdate as datetime)
set @startyear=year(@starttime)
end
if(isdate(@enddate)>0)
begin
set @endtime = cast(@enddate as datetime)
set @endyear=year(@endtime)
end
set @sql=''
declare cur CURSOR for
select right(name,4) from sysobjects where (xtype='U' or xtype='V') and name like @name+'%'
open cur
fetch next from cur into @curyear
while @@Fetch_Status=0
begin
if(isnumeric(@curyear)>0 and cast(@curyear as int)>=@startyear and cast(@curyear as int)<=@endyear)
set @sql=@sql+'select * from '+@name+@curyear+' union '
fetch next from cur into @curyear
end
close cur;
deallocate cur;
if(@sql<>'')
begin
set @sql='select '+@field+' from ('+left(@sql,len(@sql)-6)+') a'
exec(@sql)
end
END这是我delphi7中的部分代码:
str1 := 'SELECT * FROM ' + g_strDba01 + ' WHERE JH=:JH1 and RQ>=:RQ1 and RQ<=:RQ2 ORDER BY RQ'; //根据时间段查询所选择油井的数据
Close;
Sql.Clear;
Sql.Add(Str1);
ParamByName('JH1').asstring := ComboBox1.Text;
ParamByName('strname').AsString := g_strDba01;
ParamByName('stryear').asdateTime := Day1;
ParamByName('strend').asdateTime := Day2;
Open;
本来没使用存储过程,现在要调用存储过程,应该怎么调用呢?
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[p_getData]
@name varchar(20),
@field varchar(1000),
@startdate varchar(20),
@enddate varchar(20)
AS
declare @sql varchar(max),
@starttime datetime,
@endtime datetime,
@startyear int,
@endyear int,
@curyear varchar(20)
BEGIN
set @startyear=1950
set @endyear=2200
if(@field='')
set @field='*'
if(@startdate='')
set @startdate=@enddate
if(@enddate='')
set @enddate=@startdate
if(len(@startdate)=4)
set @startdate=@startdate+'0101'
if(len(@enddate)=4)
set @enddate=@enddate+'1231'
if(len(@startdate)=6)
set @startdate=@startdate+'01'
if(len(@enddate)=6 and isnumeric(@enddate)=1)
set @endtime=dateadd(dd,-1,dateadd(mm,1,cast(@enddate+'01' as datetime)))
if(isdate(@startdate)>0)
begin
set @starttime = cast(@startdate as datetime)
set @startyear=year(@starttime)
end
if(isdate(@enddate)>0)
begin
set @endtime = cast(@enddate as datetime)
set @endyear=year(@endtime)
end
set @sql=''
declare cur CURSOR for
select right(name,4) from sysobjects where (xtype='U' or xtype='V') and name like @name+'%'
open cur
fetch next from cur into @curyear
while @@Fetch_Status=0
begin
if(isnumeric(@curyear)>0 and cast(@curyear as int)>=@startyear and cast(@curyear as int)<=@endyear)
set @sql=@sql+'select * from '+@name+@curyear+' union '
fetch next from cur into @curyear
end
close cur;
deallocate cur;
if(@sql<>'')
begin
set @sql='select '+@field+' from ('+left(@sql,len(@sql)-6)+') a'
exec(@sql)
end
END这是我delphi7中的部分代码:
str1 := 'SELECT * FROM ' + g_strDba01 + ' WHERE JH=:JH1 and RQ>=:RQ1 and RQ<=:RQ2 ORDER BY RQ'; //根据时间段查询所选择油井的数据
Close;
Sql.Clear;
Sql.Add(Str1);
ParamByName('JH1').asstring := ComboBox1.Text;
ParamByName('strname').AsString := g_strDba01;
ParamByName('stryear').asdateTime := Day1;
ParamByName('strend').asdateTime := Day2;
Open;
本来没使用存储过程,现在要调用存储过程,应该怎么调用呢?
解决方案 »
- 如何修改在ibgrid显示的数据
- 如何构件WEBSERVICE来接收客户程序的HTTP POST数据?
- midas的简单问题!!!!!!!!!!!!!!!!!!!!
- 请进,一个关于窗体实例释放问题
- 请高手来帮我解决在ADO主、从表存储和触发器有效结合的问题!真诚期望您的帮忙!
- 怎样可以获得系统时间?
- 我想用C++写一个动态连结库,在Delphi下调用,可是我不太明白在C++写的Dll中的“Cout<<","Cin>>"怎么处理?
- 我有一個串口程序﹕需要從串口獲得數據然後顯示出來﹐想獲得向股票區線圖的樣式﹐請問如何做﹖用TChart可以做嗎﹖如何做﹖﹖
- 请问哪位仁兄知道?CSDN.NET的大本营怎么了?为什么进不去????
- 请问:如何制作一对多的报表(主要是思想问题)
- 有木有 加载jpg图片 时特效显示的 组件或者方法
- 用Delphi导出数据到Excel时报OLE error 800A03EC错误.
StoredProc.Parameters.ParamValues['@品名'] := MemTableEh.FieldByName('品名').AsString;
StoredProc.Parameters.ParamValues['@料号'] := MemTableEh.FieldByName('料号').AsString;
StoredProc.Parameters.ParamValues['@规格型号']:= MemTableEh.FieldByName('规格型号').AsString;
StoredProc.Parameters.ParamValues['@单位'] := MemTableEh.FieldByName('单位').AsString;
StoredProc.Parameters.ParamValues['@图号'] := MemTableEh.FieldByName('图号').AsString;
StoredProc.Parameters.ParamValues['@材质'] := MemTableEh.FieldByName('材质').AsString;
StoredProc.Parameters.ParamValues['@ABC分类'] := MemTableEh.FieldByName('ABC分类').AsString;
StoredProc.Parameters.ParamValues['@更新'] := Integer(CheckBox1.Checked);
StoredProc.ExecProc;
我要调用的存储过程,只需要4个参数: @name,@field,@startdata,@enddata.
要用到哪些控件,代码怎么写啊,我都试过好多方法了,在网上看到的都执行不通。
A,B,C,D就是你的4个参数,注意类型和单引号,按顺序赋值with adoquery1 do
beign
close;
sql.text:='exec p_getData ''A'',''B'',''C'',''D''';
open;
end;
出现错误,Missong Connection or ConnectionString.