一存储过程,需返回表中某列的值,这列是一动态列:@colName.要返回值:@balance.
总是不对,请高手指教,谢谢.
CREATE PROCEDURE GetBalance
@colName varchar(20),
@yearMonth varchar(6),
@accountId varchar(14),
@balance money output
AS
begin
declare @sql varchar(200)
set @sql=N'select @balance=' + @colName + N' from View_company where yearMonth='+ @yearMonth+N' and accountId='+@accountId
exec sp_executeSQL @sql,N'@balance money output',@balance output
end
总是不对,请高手指教,谢谢.
CREATE PROCEDURE GetBalance
@colName varchar(20),
@yearMonth varchar(6),
@accountId varchar(14),
@balance money output
AS
begin
declare @sql varchar(200)
set @sql=N'select @balance=' + @colName + N' from View_company where yearMonth='+ @yearMonth+N' and accountId='+@accountId
exec sp_executeSQL @sql,N'@balance money output',@balance output
end
CREATE PROCEDURE GetBalance
@colName nvarchar(20),
@yearMonth nvarchar(6),
@accountId nvarchar(14),
@balance money output
AS
begin
declare @sql nvarchar(200)
set @sql=N'select @balance=' + @colName + N' from View_company where yearMonth='+ @yearMonth+N' and accountId='+@accountId
exec sp_executeSQL @sql,N'@balance money output',@balance output
end
在程序中得到的返回值为NULL。请大家出手相助,谢谢这是执行情况declare @balance money
exec getbalance 'd31','200512','00038918091001',@balance out===============================
(所影响的行数为 1 行)服务器: 消息 8115,级别 16,状态 8,行 1
将 numeric 转换为数据类型 numeric 时发生算术溢出错误。
declare @sql varchar(200)
set @sql=N'select @balance=' + @colName + N' from View_company where yearMonth='+ @yearMonth+N' and accountId='+@accountId
exec sp_executeSQL @sql,N'@balance money output',@balance output改为
@balance money output
declare @sql NVARCHAR(200)
set @sql=N'select @A=' + @colName + N' from View_company where yearMonth='+ @yearMonth+N' and accountId='+@accountId
exec sp_executeSQL @sql,N'@A money output',@A=@balance output
SETLECT @balance