CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Amarundo
Amarundo

Reputation: 2397

How to return a record in a function in SQL Server 2008 r2

I want to write a function that returns in INT which is the number of records that match a certain criteria.

I tried this, and it runs... but it keeps quiet - it doesn't return anything. I guess it just runs select in the background.

ALTER FUNCTION [dbo].[f_InboundCallsPerUser](@p_User varchar(50), @p_sd datetime, @p_ed datetime)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
Return(SELECT  COUNT(*) 
FROM CallDetail
WHERE LocalUserID = @p_user
AND InitiatedDate BETWEEN @p_sd AND @p_ed
AND UPPER(CallDirection) = 'OUTBOUND'
AND LineDurationSeconds > 0);
END;

How do you do that?

Upvotes: 0

Views: 136

Answers (1)

Martin Smith
Martin Smith

Reputation: 454019

Use

SELECT [dbo].[f_InboundCallsPerUser](@p_User, @p_sd , @p_ed)

I guess you are probably EXEC-ing it. in which case you need to do

DECLARE @Result INT
EXEC @Result =[dbo].[f_InboundCallsPerUser] @p_User, @p_sd , @p_ed 
SELECT @Result

to see the returned value.

Upvotes: 2

Related Questions