sql - 收到以下错误“函数中包含的选择语句无法将数据返回给客户端”

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 语句无法将数据返回给客户端

标签： sqlsql-serverfunction

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