在函数sql server 2008中获取错误 [英] getting error in function sql server 2008

问题描述

ALTER FUNCTION [dbo].[FNGetDataRowCount](@DatasetID AS INT)  
RETURNS BIGINT  
AS  
  BEGIN
  	

  DECLARE @retval AS BIGINT
  DECLARE @temp TABLE (
 
                table_name sysname ,  
                row_count INT ,
                reserved_size VARCHAR(50),  
                data_size VARCHAR(50),  
                index_size VARCHAR(50),  
                unused_size VARCHAR(50)
                )  
                  
                DECLARE @datasetName varchar(100)  
                SET @datasetName = ''[''+Cast(@DatasetID AS varchar)+'']''  
                  

INSERT @temp  
    EXEC sp_spaceused @datasetName  

 SELECT @retval=row_count from @temp  WHERE table_name=@DatasetID
 
 
 RETURN @retval
 
  END

上面的函数给出错误-

above function giving error-

Msg 443, Level 16, State 14, Procedure FNGetDataRowCount, Line 22
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

推荐答案

您不能从函数中调用存储过程.

you cannot call a stored procedure from function..

此处指出: ^ ]:

该调用的问题在于，无法保证存储过程在运行时不会更改数据库的状态.因此，SQL Server不允许您在函数内部使用过程.

作为解决方法，您应使用例如SELECT语句代替在中间表中插入数据的过程.

As pointed out here: Call a stored procedure in select query[^]:

The problem with that call is that there''s no guarantee that the stored procedure won''t change the state of the database if it''s run. Because of this, the SQL server won''t allow you to use a procedure inside a function.

As a workaround you should use for example SELECT statements instead of the procedures to insert data in the intermediate table.

这篇关于在函数sql server 2008中获取错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！