How execute user defined function sql which is returning a table?

Question

i am working with SQL server 2012.

i have created a function in sql like this:

CREATE FUNCTION [dbo].[Fn_IsCompanyExistInUserLocation](@CompanyId int)

RETURNS @Result table
(
UserId int null
)
AS
BEGIN
    insert into @Result(UserId) select UL.UserId FROM [RRT_Reporting].[dbo].[UserLocation] as UL where UL.CompanyId=@CompanyId and UL.LocationType='COMPANY'


    RETURN 
END

i am not sure its right way or not.

now i am trying to execute this function like :

DECLARE @ret as table (UserId int null) 

EXEC @ret = [dbo].[Fn_IsCompanyExistInUserLocation] @CompanyId= 10; 

select UserId from @ret

and i am getting error:

Msg 137, Level 16, State 1, Line 6
Must declare the scalar variable "@ret".

You don't need to tell us e.g. that you'll mark the answer that works as accepted. That's how SO works. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 27, 2014 at 7:57

shree.pat18 · Accepted Answer · 2014-03-27 08:06:41Z

2

Try this:

DECLARE @ret table (UserId int null) 
insert into @ret select * from [dbo].[Fn_IsCompanyExistInUserLocation](10)
select UserID from @ret

edited Mar 27, 2014 at 8:06

answered Mar 27, 2014 at 7:51

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

R K Sharma Over a year ago

it gives an error: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'into'.

R K Sharma · Accepted Answer · 2014-03-27 08:03:13Z

0

I got it:

SELECT *
FROM [dbo].[Fn_IsCompanyExistInUserLocation](10)

answered Mar 27, 2014 at 8:03

R K Sharma

8759 gold badges23 silver badges43 bronze badges

4 Comments

shree.pat18 Over a year ago

So are you using @ret variable at all now?

R K Sharma Over a year ago

dude i tried to test Fn_IsCompanyExistInUserLocation function.

R K Sharma Over a year ago

technet.microsoft.com/en-US/enus/library/… is really helpfull

shree.pat18 Over a year ago

Yes. Always look at the official docs first when starting out!

Collectives™ on Stack Overflow

How execute user defined function sql which is returning a table?

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related