CODEWITHSUNDEEP
sqlsql-server-2008
hncl
hncl

Reputation: 2295

SQL Server 2008 - Function error

I am new to sql programming; trying to develop this function to get rows of clients who have a specific number of visits from a view:

    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

I get the following error:

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

I would appreciate your support. Thanks in advance.

Upvotes: 1

Views: 305

Answers (2)

Taryn
Taryn

Reputation: 247620

The error is telling you that your subquery is returning too many rows. You need to return only one row is you are assigning the result to a variable.

Change

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

Upvotes: 0

web_bod
web_bod

Reputation: 5758

Your problem is here:

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

@Count is expecting a number - you're giving it a bunch of rows, try:

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

Upvotes: 1

Related Questions