How do I call user defined function in the select SQL

Hi Experts,

I have created an user defined function [GetUniqueOppbyDealer] and want to add it in the select clause as a column.
Can you help me on

1)check if my query is correct with the user defined function
2)In [GetUniqueOppbyDealer] , its dealerid and date have to be controlled  by the where conditions from the main dataset.


select sum(ad.[WidgetInteraction]) as pencils,
cte.dealstarts, cte.alternatedeals,
cte.createddeals,
mmd_feed.[dbo].[GetUniqueOppbyDealer] as uniqueopportunity
from makemydeal_com.[dbo].[adobe_Standard] ad
inner join cte cte on cte.dealerid=ad.dealerid
where cte.dealerid=99
and date between '2015-04-01' and '2015-05-15'
group by cte.dealstarts, cte.alternatedeals, cte.createddeals
tanj1035Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
It seems to me that your function should be taking some parameter like the dealerid.

SELECT mmd_feed.dbo.GetUniqueOppbyDealer (dealerid) AS Opp
FROM makemydeal_com.dob.adobe_standard
0
MacNuttinCommented:
Think of user defined function like a getdate() and like Brian says you need to pass it dealerID so:
 select sum(ad.[WidgetInteraction]) as pencils,
 cte.dealstarts, cte.alternatedeals,
 cte.createddeals,
 mmd_feed.[dbo].[GetUniqueOppbyDealer](ad.dealerid) as uniqueopportunity
 from makemydeal_com.[dbo].[adobe_Standard] ad
 inner join cte cte on cte.dealerid=ad.dealerid
 where cte.dealerid=99
 and date between '2015-04-01' and '2015-05-15'
 group by cte.dealstarts, cte.alternatedeals, cte.createddeals
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Even your function don't accept parameters (as GETDATE for example) you'll always need the parenthesis:
SELECT SUM(ad.[WidgetInteraction]) as pencils, 
   cte.dealstarts, cte.alternatedeals, cte.createddeals, 
   mmd_feed.[dbo].GetUniqueOppbyDealer() as uniqueopportunity
FROM makemydeal_com.[dbo].[adobe_Standard] ad
  INNER JOIN cte cte on cte.dealerid=ad.dealerid
WHERE cte.dealerid=99 AND date between '2015-04-01' and '2015-05-15'
GROUP BY cte.dealstarts, cte.alternatedeals, cte.createddeals 

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tanj1035Author Commented:
Thanks for your replies. Sorry, Experts, I did not show my query in a correct way. Please see my revised query below and help me on

1)check if my query is correct with the user defined function
 2)In [GetUniqueOppbyDealer] , its dealerid and date have to be controlled  by the where conditions from the main dataset.

declare @Dealerid int
declare @StartDate date
declare @EndDate date
set @StartDate ='2015-04-01'
set @EndDate='2015-04-30'
set @Dealerid = 99;



with cte as (select count(*) as dealstarts,dealerid,
sum(case when AlternativeDeal='1'then 1 else 0 end)as AlternateDeals,
sum(case when Manualsend='1'then 1 else 0 end) as CreatedDeals
from MMD_Feed..Deal
where dealerid=@Dealerid
and submittedtimestamp between @StartDate and @EndDate
and alternativedeal is null
and dealerid in (select dealerid from MMD_Feed..Dart)
group by dealerid)

select sum(ad.[WidgetInteraction]) as pencils,
cte.dealstarts,
cte.alternatedeals,
cte.createddeals,
[GetUniqueOppbyDealer](@dealerid, @startDate, @EndDate) as uniqueopportunity
from makemydeal_com.[dbo].[adobe_Standard] ad
inner join cte cte on cte.dealerid=ad.dealerid
where cte.dealerid= @Dealerid
and date between @StartDate and @EndDate
group by cte.dealstarts, cte.alternatedeals, cte.createddeals,[GetUniqueOppbyDealer](@dealerid, @startDate, @EndDate)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you get any error when you run that code or it just don't do what you're expecting it to do?
0
tanj1035Author Commented:
Yes, the error message is "Incorrect syntax near '@Dealerid' which is referring to "[GetUniqueOppbyDealer](@Dealerid, @startDate, @EndDate) as uniqueopportunity".

So, I think there is query error when it called parameter with the userdefinedfunction.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post that function definition? Or at least the function header?
0
tanj1035Author Commented:
Sure, this is the function

CREATE Function [dbo].[GetUniqueOppbyDealer]

(@start_dt date,
@end_dt date,
@dealerid int)

Returns @GetUniqueOppbyDealer table

(alias varchar(250),

submitteddate date)

begin



while @start_dt < @end_dt

Begin



insert into @GetUniqueOppbyDealer

select distinct alias, @start_dt

from dbo.dealview

where dealstatus= 'sent'

and submittedtimestamp >=@start_dt and submittedtimestamp < dateadd(d,1,@start_dt)

and alias not in (select alias from @GetUniqueOppbyDealer)
and dealerID = dealerid

set @start_dt = dateadd(d,1,@start_dt)

end
return
end



GO
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The function is expecting the variables in this order: [GetUniqueOppbyDealer]
(@start_dt date, @end_dt date, @dealerid int)

and you are passing in a different order: [GetUniqueOppbyDealer](@dealerid, @startDate, @EndDate)

Just change the order in the SELECT statement to: [GetUniqueOppbyDealer](@startDate, @EndDate, @dealerid)
0
tanj1035Author Commented:
I changed the order, but still got the error message " Incorrect syntax near '@StartDate'"

Also, I tried "mmd_feed.dbo.[GetUniqueOppbyDealer](@StartDate,@EndDate,@Dealerid) as uniqueopportunity " and got the error message "Cannot find either column "mmd_feed" or the user-defined function or aggregate "mmd_feed.dbo.GetUniqueOppbyDealer", or the name is ambiguous."
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I suppose the mmd_feed is the database name, right?
And you are joining with tables from other database name makemydeal_com. Correct?
Check if in both databases exists the same function and if so, then also check if they are strictly equal (header and body).
0
tanj1035Author Commented:
Hi, I checked both database. The function is in mmd_feed only. So any other thoughts?? Thanks.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Maybe the problem is that function returns a table (Returns @GetUniqueOppbyDealer table) and should return a single value (varchar, integer, date, ...) so it can work as a field in the SELECT column list.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.