1. 用参数: SELECT name,status,time FROM table WHERE name= :name // '张三' String AND time >= :DATE1 // DATE('19-Apr-00 0:00') DateTime AND time <= :DATE2 // DATE('19-Jan-01 6:00') DateTime2.动态写SQL语句: WITH query1 DO BEGIN CLOSE; SQL.Clear; SQL.APPEND('SELECT name,status,time FROM table '); SQL.APPEND('WHERE name= ') SQL.APPEND(format('%s',['张三']); SQL.APPEND(format('AND time >= %s',[ DATETOSTR('19-Apr-00 0:00')]); SQL.APPEND(format('AND time =< %s',[ DATETOSTR('19-Jan-01 6:00')]); OPEN; END;
libin_ftsafe(子陌红尘): SQL Server 2000不支持数组,所以无法将动态SQL执行结果保存到数组中输出;以下是一个以变量输出动态SQL执行结果的例子: ----------------------------------------------------------------------------- --生成测试数据 create table #T(id int,name varchar(10)) insert into #T select 1,'AAAA' insert into #T select 2,'AAAB' insert into #T select 3,'AAAC' insert into #T select 4,'AAAD' insert into #T select 5,'AAAE'--执行查询 declare @count int,@sql nvarchar(4000) set @sql = N'select @count=count(*) from #T' exec sp_executesql @sql,N'@count int out',@count out--查看动态SQL执行的输出参数 select @count--输出结果 /* 无列名 ------- 5 */
SELECT name,status,time
FROM table
WHERE name= :name // '张三' String
AND time >= :DATE1 // DATE('19-Apr-00 0:00') DateTime
AND time <= :DATE2 // DATE('19-Jan-01 6:00') DateTime2.动态写SQL语句:
WITH query1 DO
BEGIN
CLOSE;
SQL.Clear;
SQL.APPEND('SELECT name,status,time FROM table ');
SQL.APPEND('WHERE name= ')
SQL.APPEND(format('%s',['张三']);
SQL.APPEND(format('AND time >= %s',[ DATETOSTR('19-Apr-00 0:00')]);
SQL.APPEND(format('AND time =< %s',[ DATETOSTR('19-Jan-01 6:00')]);
OPEN;
END;
SQL Server 2000不支持数组,所以无法将动态SQL执行结果保存到数组中输出;以下是一个以变量输出动态SQL执行结果的例子:
-----------------------------------------------------------------------------
--生成测试数据
create table #T(id int,name varchar(10))
insert into #T select 1,'AAAA'
insert into #T select 2,'AAAB'
insert into #T select 3,'AAAC'
insert into #T select 4,'AAAD'
insert into #T select 5,'AAAE'--执行查询
declare @count int,@sql nvarchar(4000)
set @sql = N'select @count=count(*) from #T'
exec sp_executesql @sql,N'@count int out',@count out--查看动态SQL执行的输出参数
select @count--输出结果
/*
无列名
-------
5
*/