--示例
create proc p_qry
@ch_id int, --要计算的序号
@re real out --输出结果
as
declare @s nvarchar(4000)
select @s='select @re=avg(['+name+']) from [data]'
from syscolumns
where object_id('data')=id
exec sp_executesql @s,N'@re real out',@re out
go
create proc p_qry
@ch_id int, --要计算的序号
@re real out --输出结果
as
declare @s nvarchar(4000)
select @s='select @re=avg(['+name+']) from [data]'
from syscolumns
where object_id('data')=id
exec sp_executesql @s,N'@re real out',@re out
go
declare @re real
exec p_qry 2,@re out
as
declare @sql varchar(8000)
set @sql='select AVG(CH'+right('00'+cast(@ch_id as varchar),2)+') ch from DATA'
exec(@sql)
go--调用
exec sp_1 2
CREATE PROCEDURE sp_GetFieldValue
(
@cTalbeName VARCHAR(50) , --数据表名
@iFieldOrder INT --字段的序号
)
AS
DECLARE @cExecSQL VARCHAR(8000) --执行的SQL语句 SELECT @cExecSQL = 'SELECT AVG('+name+') FROM ['+@cTalbeName+']'
FROM syscolumns
WHERE OBJECT_ID(@cTalbeName) = ID AND colorder = @iFieldOrder EXEC (@cExecSQL)GO