CODEWITHSUNDEEP
sql-server-2008-r2
praba
praba

Reputation: 31

SQL server when try to access udf function created with dbo "Invalid object name 'dbo.Function_Client'."

Hi I have created an udf function in sqlserver 2008... when i try to access that it gives an error "Invalid object name 'dbo.Function_Client" I want to return the count of records from table and assign it to variable to return...so its a scalar function... inside i have implemented if else logic..

my udf below

CREATE FUNCTION [dbo].[Function_Clients] (@ClientAlias varchar(10) ,
  @TimePeriod int, @TypeOfTimePeriod varchar(1))

RETURNS INT
AS
BEGIN
  DECLARE @COUNT INT;

  IF(@ClientAlias='AEP' AND @TypeOfTimePeriod ='M')
     Set @COUNT =  (SELECT DISTINCT COUNT(*) AS 'NO_AEP' from dbo.Engagement INNER JOIN
        dbo.Client ON dbo.Engagement.ClientIdentifier = dbo.Client.ClientIdentifier 
    WHERE (dbo.ClientInvolvementRole.ClientInvolvementID = '1356790AERTY') 
    AND (CONVERT(datetime, dbo.Engagement.EndDate, 103) 
    <= CONVERT(datetime, DATEADD(MONTH, @TimePeriod , GETDATE()), 103))  


ELSE

---SAME ABOVE CODE BUT REPLACING MONTH WITH DAY AS 
---"DATEADD(DAY,@TimePeriod , GETDATE()), 103)" IN WHERE CONDITION  .

...if else (for various client alias)

RETURN @COUNT;  
END
GO

for each @clientalias i want to add month part and part based on my input in the @TypeOfTimePeriod . For example @TypeOfTimePeriod ='M' means month or 'D' means its day to add in the dateadd part.

i have called like this select * from dbo.Clients('AEP',12,'M')

but its showing error Invalid object name 'dbo.Function_NoOfClients'.

i can able to access the view with dbo.view1... but not this udf... any problem in my udf..please help me...

Upvotes: 2

Views: 7840

Answers (1)

Oleg Dok
Oleg Dok

Reputation: 21776

Use

SELECT dbo.Clients('AEP',12,'M')

Note difference in usage of Table valued and scalar functions

Upvotes: 7

Related Questions