Question

我是新来的Sql编程; 试图发展这一功能, 以吸引有特定访问次数的客户:

    ALTER FUNCTION [dbo].[fn_NumberOfVisit] 
(
@nv int
)
RETURNS varchar(500)
AS
BEGIN
DECLARE @ret varchar(500)

 select *
from (
    select 
        *,
        rn = row_number() over (partition by ClientId order by VisitId)
    from
        Visit
) activityWithRn
inner join vw_MasterView on  vw_MasterView.VisitId = activityWithRn.VisitId
where activityWithRn.rn =@nv

RETURN @ret

END

我得到以下错误:

   Select statements included within a function cannot return data to a client

非常感谢你们的支持,多谢你的支持

Answer 1

错误正在告诉您您的子查询返回的行太多。您只需要返回一行, 就是您正在将结果指定为变量。

变动变动

set @Count = ( select *
from (
    select 
        *,
        rn = row_number() over (partition by ClientId order by VisitId)
    from
        Visit

将 to

set @Count = ( select count(*)
from (
    select 
        *,
        rn = row_number() over (partition by ClientId order by VisitId)
    from
        Visit

Answer 2

你的问题就在这里:

set @Count = ( select *
from (
    select 
        *,

@Count在等待一个数字,

set @Count = ( select Count(*)
from (
    select

友情链接