收到以下错误"SELECT STATEMENTS INCLUDED WITHIN A FUNCTION CANNOT RETURN DATA TO A CLIENT"

Question

我正在尝试创建以下函数：

Create function mastercount
(
@table nvarchar(50)
, @policy nvarchar(20)
, @firstname nvarchar(20)
, @lastname nvarchar (20)
)    
Returns nvarchar (50)
As
Begin
Declare @count int =''
If @table ='A'
Select count (*)
from A
where policy = @policy and firstname = @firstname and lastname = @lastname    
Else If @table ='B'
Select count (*)
from A
where policy = @policy and firstname = @firstname and lastname = @lastname    
Else If @table ='A'
Select count (*)
from A
where policy = @policy and firstname = @firstname and lastname = @lastname
Return @count;
End

但是我收到一个错误：

函数中包含的SELECT语句无法将数据返回到客户端

Answer 1

你可以用这种方式试试。变量@count被分配了一个空字符串。我将其更改为默认值0。此外，SELECT语句正试图将记录返回给客户端。现在，它们分别被分配给变量@count。

Create function mastercount
(
@table nvarchar(50)
, @policy nvarchar(20)
, @firstname nvarchar(20)
, @lastname nvarchar (20)
)    
Returns int
As
Begin
Declare @count int=0;
If @table ='A'
Select @count=count(*)
from A
where policy = @policy and firstname = @firstname and lastname = @lastname    
Else If @table ='B'
Select @count=count(*)
from A
where policy = @policy and firstname = @firstname and lastname = @lastname    
Else If @table ='A'
Select @count=count(*)
from A
where policy = @policy and firstname = @firstname and lastname = @lastname
Return @count;
End