之前的帖子(SQL Server自定义函数的问题),可能是没有描述好,解决的方法绕过了问题的关键,在此将问题给的更有针对性些,望大家帮忙
IF EXISTS (SELECT name from sysobjects where name='proc_1' AND type='FN')
DROP FUNCTION proc_1
GOCREATE FUNCTION proc_1
(@STATTYPE numeric(5),
@VARTABLE varchar(30),
@VARKBP numeric(5),
@VARKPI_NO numeric(5),
@VARYYYYMMDD1 varchar(20),
@VARYYYYMMDD2 varchar(20)
)
RETURNS numeric(13) AS
BEGIN
declare @result numeric(20)
declare @SQL_STMT varchar(200)
declare @VARYYYYMMDD1_00 varchar(20)
declare @VARYYYYMMDD2_00 varchar(20)
IF @STATTYPE=1
begin
set @VARYYYYMMDD1_00 = @VARYYYYMMDD1 + '00'
set @VARYYYYMMDD2_00 = @VARYYYYMMDD2 + '00'
set @SQL_STMT='SELECT top 1 TIME_ID FROM '+@VARTABLE+' WHERE KBP='+@VARKBP+' AND KPI_NO='+@VARKPI_NO+' AND TIME_ID>='+@VARYYYYMMDD1_00+' AND TIME_ID<'+@VARYYYYMMDD2_00+' ORDER BY VALUEMIN'
--set @result=(SELECT top 1 TIME_ID FROM @VARTABLE WHERE KBP=@VARKBP AND KPI_NO=@VARKPI_NO AND TIME_ID>=@VARYYYYMMDD1_00 AND TIME_ID<@VARYYYYMMDD2_00 ORDER BY VALUEMIN)
set @result= exec(@SQL_STMT)
end
ELSE
begin
set @result=(SELECT top 1 MINVALUETIME FROM PM_TEMP_DAY WHERE KBP=@VARKBP AND KPI_NO=@VARKPI_NO ORDER BY VALUEMIN)
end
return @result
END
在IF语句成立时,如何得到@result?
IF EXISTS (SELECT name from sysobjects where name='proc_1' AND type='FN')
DROP FUNCTION proc_1
GOCREATE FUNCTION proc_1
(@STATTYPE numeric(5),
@VARTABLE varchar(30),
@VARKBP numeric(5),
@VARKPI_NO numeric(5),
@VARYYYYMMDD1 varchar(20),
@VARYYYYMMDD2 varchar(20)
)
RETURNS numeric(13) AS
BEGIN
declare @result numeric(20)
declare @SQL_STMT varchar(200)
declare @VARYYYYMMDD1_00 varchar(20)
declare @VARYYYYMMDD2_00 varchar(20)
IF @STATTYPE=1
begin
set @VARYYYYMMDD1_00 = @VARYYYYMMDD1 + '00'
set @VARYYYYMMDD2_00 = @VARYYYYMMDD2 + '00'
set @SQL_STMT='SELECT top 1 TIME_ID FROM '+@VARTABLE+' WHERE KBP='+@VARKBP+' AND KPI_NO='+@VARKPI_NO+' AND TIME_ID>='+@VARYYYYMMDD1_00+' AND TIME_ID<'+@VARYYYYMMDD2_00+' ORDER BY VALUEMIN'
--set @result=(SELECT top 1 TIME_ID FROM @VARTABLE WHERE KBP=@VARKBP AND KPI_NO=@VARKPI_NO AND TIME_ID>=@VARYYYYMMDD1_00 AND TIME_ID<@VARYYYYMMDD2_00 ORDER BY VALUEMIN)
set @result= exec(@SQL_STMT)
end
ELSE
begin
set @result=(SELECT top 1 MINVALUETIME FROM PM_TEMP_DAY WHERE KBP=@VARKBP AND KPI_NO=@VARKPI_NO ORDER BY VALUEMIN)
end
return @result
END
在IF语句成立时,如何得到@result?
IF EXISTS (SELECT NAME
FROM SYSOBJECTS
WHERE NAME = 'proc_1'
AND TYPE = 'P')
DROP PROC PROC_1 CREATE PROC PROC_1(
@STATTYPE FLOAT,
@VARTABLE VARCHAR(30),
@VARKBP FLOAT,
@VARKPI_NO FLOAT,
@VARYYYYMMDD1 VARCHAR(20),
@VARYYYYMMDD2 VARCHAR(20),
@result FLOAT OUTPUT)
AS
BEGIN
DECLARE
@result NUMERIC(20)
DECLARE
@SQL_STMT VARCHAR(200)
DECLARE
@VARYYYYMMDD1_00 VARCHAR(20)
DECLARE
@VARYYYYMMDD2_00 VARCHAR(20)
IF @STATTYPE = 1
BEGIN
SET @VARYYYYMMDD1_00 = @VARYYYYMMDD1 + '00'
SET @VARYYYYMMDD2_00 = @VARYYYYMMDD2 + '00'
SET @SQL_STMT = 'SELECT top 1 TIME_ID FROM ' + @VARTABLE + ' WHERE KBP=' + @VARKBP + ' AND KPI_NO=' + @VARKPI_NO + ' AND TIME_ID>=' + @VARYYYYMMDD1_00 + ' AND TIME_ID<' + @VARYYYYMMDD2_00 + ' ORDER BY VALUEMIN'
--set @result=(SELECT top 1 TIME_ID FROM @VARTABLE WHERE KBP=@VARKBP AND KPI_NO=@VARKPI_NO AND TIME_ID>=@VARYYYYMMDD1_00 AND TIME_ID<@VARYYYYMMDD2_00 ORDER BY VALUEMIN)
SET @result = EXEC ( @SQL_STMT )
END
ELSE
BEGIN
SET @result = (SELECT top 1 MINVALUETIME
FROM PM_TEMP_DAY
WHERE KBP = @VARKBP
AND KPI_NO = @VARKPI_NO
ORDER BY VALUEMIN)
END
END
(
@STATTYPE numeric(5),
@VARTABLE nvarchar(30),
@VARKBP numeric(5),
@VARKPI_NO numeric(5),
@VARYYYYMMDD1 nvarchar(20),
@VARYYYYMMDD2 nvarchar(20),
@result numeric(20) OUTPUT
)declare @SQL_STMT nvarchar(200)
declare @VARYYYYMMDD1_00 nvarchar(20)
declare @VARYYYYMMDD2_00 nvarchar(20)IF @STATTYPE=1
begin
set @VARYYYYMMDD1_00 = @VARYYYYMMDD1 + '00'
set @VARYYYYMMDD2_00 = @VARYYYYMMDD2 + '00'
set @SQL_STMT='SELECT top 1 @result=TIME_ID FROM '+@VARTABLE+' WHERE KBP='+@VARKBP+' AND KPI_NO='+@VARKPI_NO+' AND TIME_ID>='+@VARYYYYMMDD1_00+' AND TIME_ID<'+@VARYYYYMMDD2_00+' ORDER BY VALUEMIN'
exec sp_executesql @SQL_STMT,N'@result numeric(20) OUTPUT',@result OUTPUT
end
ELSE
begin
set @result=(SELECT top 1 MINVALUETIME FROM PM_TEMP_DAY WHERE KBP=@VARKBP AND KPI_NO=@VARKPI_NO ORDER BY VALUEMIN)
end
END
DROP ProceDure proc_1
GOCREATE ProceDure proc_1
(@STATTYPE numeric(5),
@VARTABLE varchar(30),
@VARKBP numeric(5),
@VARKPI_NO numeric(5),
@VARYYYYMMDD1 varchar(20),
@VARYYYYMMDD2 varchar(20),
@result numeric(20) OUTPUT
)
As
BEGIN
declare @SQL_STMT Nvarchar(2000)
declare @VARYYYYMMDD1_00 varchar(20)
declare @VARYYYYMMDD2_00 varchar(20)
IF @STATTYPE=1
begin
set @VARYYYYMMDD1_00 = @VARYYYYMMDD1 + '00'
set @VARYYYYMMDD2_00 = @VARYYYYMMDD2 + '00'
set @SQL_STMT='SELECT top 1 @result=TIME_ID FROM '+@VARTABLE+' WHERE KBP='+@VARKBP+' AND KPI_NO='+@VARKPI_NO+' AND TIME_ID>='+@VARYYYYMMDD1_00+' AND TIME_ID<'+@VARYYYYMMDD2_00+' ORDER BY VALUEMIN'
--set @result=(SELECT top 1 TIME_ID FROM @VARTABLE WHERE KBP=@VARKBP AND KPI_NO=@VARKPI_NO AND TIME_ID>=@VARYYYYMMDD1_00 AND TIME_ID<@VARYYYYMMDD2_00 ORDER BY VALUEMIN)
EXEC sp_executesql @SQL_STMT, N'@result Int output', @result output
end
ELSE
begin
set @result=(SELECT top 1 MINVALUETIME FROM PM_TEMP_DAY WHERE KBP=@VARKBP AND KPI_NO=@VARKPI_NO ORDER BY VALUEMIN)
end
END
GO
EXEC sp_executesql @SQL_STMT, N'@result Int output', @result output
FROM SYSOBJECTS
WHERE NAME = 'proc_1'
AND XTYPE = 'P')
DROP PROCEDURE PROC_1 GO
CREATE PROCEDURE PROC_1(
@STATTYPE NUMERIC(5),
@VARTABLE VARCHAR(30),
@VARKBP NUMERIC(5),
@VARKPI_NO NUMERIC(5),
@VARYYYYMMDD1 VARCHAR(20),
@VARYYYYMMDD2 VARCHAR(20),
@result NUMERIC(20) OUTPUT)
AS
BEGIN
DECLARE
@SQL_STMT NVARCHAR(2000)
DECLARE
@VARYYYYMMDD1_00 VARCHAR(20)
DECLARE
@VARYYYYMMDD2_00 VARCHAR(20)
IF @STATTYPE = 1
BEGIN
SET @VARYYYYMMDD1_00 = @VARYYYYMMDD1 + '00'
SET @VARYYYYMMDD2_00 = @VARYYYYMMDD2 + '00'
SET @SQL_STMT = 'SELECT top 1 @result=TIME_ID FROM ' + @VARTABLE + ' WHERE KBP=' + @VARKBP + ' AND KPI_NO=' + @VARKPI_NO + ' AND TIME_ID>=' + @VARYYYYMMDD1_00 + ' AND TIME_ID<' + @VARYYYYMMDD2_00 + ' ORDER BY VALUEMIN'
--set @result=(SELECT top 1 TIME_ID FROM @VARTABLE WHERE KBP=@VARKBP AND KPI_NO=@VARKPI_NO AND TIME_ID>=@VARYYYYMMDD1_00 AND TIME_ID<@VARYYYYMMDD2_00 ORDER BY VALUEMIN)
EXEC SP_EXECUTESQL @SQL_STMT ,
N'@result Int output' ,
@result OUTPUT
END
ELSE
BEGIN
SET @result = (SELECT top 1 MINVALUETIME
FROM PM_TEMP_DAY
WHERE KBP = @VARKBP
AND KPI_NO = @VARKPI_NO
ORDER BY VALUEMIN)
END
ENDGO