我把查询条件做为参数
存储过程:CREATE procedure Rep_jxcqsdb
@StrWhere varchar(1000)=''
as
/*--------------------- 平均行程趋势对比 ----------------*/
declare @StrSql varchar(1500)
begin
if @StrWhere=''
set @StrSql='SELECT 核算年份, 核算月份, COUNT(OrderID) AS 发车数, SUM(行驶里程) AS 行驶总里程,ROUND(SUM(行驶里程) / COUNT(OrderID), 2) AS 单趟平均行程 FROM L_LNG_Truck_Costing
GROUP BY 核算年份, 核算月份 order by 核算年份'
else
set @StrSql='SELECT 核算年份, 核算月份, COUNT(OrderID) AS 发车数, SUM(行驶里程) AS 行驶总里程,ROUND(SUM(行驶里程) / COUNT(OrderID), 2) AS 单趟平均行程 FROM L_LNG_Truck_Costing
where @StrWhere GROUP BY 核算年份, 核算月份 order by 核算年份'
end;
exec (@StrSql)
GO-----
delphi:
ProcRep_jxcqsdb.Close;
ProcRep_jxcqsdb.Parameters.ParamByName('@StrWhere').Value:= GetSqlWhere;
ProcRep_jxcqsdb.ExecProc;
ProcRep_jxcqsdb.Open;报错没有声明变量
存储过程:CREATE procedure Rep_jxcqsdb
@StrWhere varchar(1000)=''
as
/*--------------------- 平均行程趋势对比 ----------------*/
declare @StrSql varchar(1500)
begin
if @StrWhere=''
set @StrSql='SELECT 核算年份, 核算月份, COUNT(OrderID) AS 发车数, SUM(行驶里程) AS 行驶总里程,ROUND(SUM(行驶里程) / COUNT(OrderID), 2) AS 单趟平均行程 FROM L_LNG_Truck_Costing
GROUP BY 核算年份, 核算月份 order by 核算年份'
else
set @StrSql='SELECT 核算年份, 核算月份, COUNT(OrderID) AS 发车数, SUM(行驶里程) AS 行驶总里程,ROUND(SUM(行驶里程) / COUNT(OrderID), 2) AS 单趟平均行程 FROM L_LNG_Truck_Costing
where @StrWhere GROUP BY 核算年份, 核算月份 order by 核算年份'
end;
exec (@StrSql)
GO-----
delphi:
ProcRep_jxcqsdb.Close;
ProcRep_jxcqsdb.Parameters.ParamByName('@StrWhere').Value:= GetSqlWhere;
ProcRep_jxcqsdb.ExecProc;
ProcRep_jxcqsdb.Open;报错没有声明变量
然后用
ADOStoredProc1.Parameters.CreateParameter()成生参数
最后赋值参数
然后执行。
ADOQuery1.Exec;
ProcRep_jxcqsdb.parameters.refrsh;//需要加
ProcRep_jxcqsdb.Parameters.ParamByName('@StrWhere').Value:= GetSqlWhere;
ProcRep_jxcqsdb.ExecProc;
ProcRep_jxcqsdb.Open;
where @StrWhere GROUP BY 核算年份, 核算月份 order by 核算年份'应该是
where '+@StrWhere+' GROUP BY 核算年份, 核算月份 order by 核算年份'否则当然会出现变量没有定义的情况了
@PrmOrderDate DATETIME, @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500)
DECLARE @OrderMonth INT-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
/* Build the name of the table. */
SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
'Sales' +
/* Build a VALUES clause. */
' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
' @InsOrdMonth, @InsDelDate)'/* Set the value to use for the order month because
functions are not allowed in the sp_executesql parameter
list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate)EXEC sp_executesql @InsertString,
N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
@InsOrdMonth INT, @InsDelDate DATETIME',
@PrmOrderID, @PrmCustomerID, @PrmOrderDate,
@OrderMonth, @PrmDeliveryDate