存储过程如下:
DROP PROCEDURE sele_ratio;
GO
CREATE PROC sele_ratio
AS
DECLARE @device_id VARCHAR(50)
DECLARE @today_checkTime DATETIME
DECLARE @yes_checkTime DATETIME
DECLARE @sql1 nvarchar(MAX)
DECLARE @sql2 nvarchar(MAX)
DECLARE @result1 INT
DECLARE @result2 INT
BEGIN
SET @today_checkTime='2016-01-28 00:35:00';
SET @yes_checkTime='2016-01-28 03:50:00.000';
SET @device_id='0171170313120010';
SET @sql1='SELECT @a1= SUM(SmallBusFlow+SmallTruckFlow+LarBusFlow+MedTruckFlow+LarTruckFlow+SupLarTruckFlow+ContainerFlow+TractorFlow+MotorFlow)'
+' FROM tis_vehdetail_'+CAST(@device_id AS VARCHAR(50))+' WHERE RecNo = '
+'(SELECT RecNo FROM tis_vehbasic_'+@device_id+' WHERE CheckDate = '+''''+CONVERT(VARCHAR,@today_checkTime,120)+''''+')'
SET @sql2='SELECT @a2= SUM(SmallBusFlow+SmallTruckFlow+LarBusFlow+MedTruckFlow+LarTruckFlow+SupLarTruckFlow+ContainerFlow+TractorFlow+MotorFlow)'
+' FROM tis_vehdetail_'+CAST(@device_id AS VARCHAR(50))+' WHERE RecNo = '
+'(SELECT RecNo FROM tis_vehbasic_'+@device_id+' WHERE CheckDate = '+''''+CONVERT(VARCHAR,@yes_checkTime,120)+''''+')'
--EXEC(@sql)
EXEC sp_executesql @sql1, N'@a1 int output',@result1 OUTPUT
EXEC sp_executesql @sql2, N'@a2 int output',@result2 output
SET @result2=@result2;
END
GO
- 该存储过程在navicate以及sqlserver自带的编译器里调试没有问题;
但是在函数中调用该存储过程时,出现错误,如下截图