sql function not retrieving table and giving error

Question

I have written following function in SQLServer 2005

Following is the function:

create function fnBillParticulars()
return table
as
return (select * from billParticulars where Id='2425')
go

Its giving me following errors:

1.Msg 156, Level 15, State 1, Procedure fnBillParticulars, Line 2
  Incorrect syntax near the keyword 'return'.

2.Msg 178, Level 15, State 1, Procedure fnBillParticulars, Line 4
  A RETURN statement with a return value cannot be used in this context.

what can be the mistake?

Please help me.

TechDo · Accepted Answer · 2013-05-16 09:50:43Z

3

Please try:

create function fnBillParticulars()
returns table
as
return (select * from billParticulars where Id='2425')
go

answered May 16, 2013 at 9:50

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

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

1 Comment

Freelancer Over a year ago

Thank you. , was unware about 'returns'. Thank you.

John Woo · Accepted Answer · 2013-05-16 09:50:48Z

1

you can alternatively create a VIEW on this,

CREATE VIEW fnBillParticulars
AS
select * 
from billParticulars 
where where Id='2425'

or if you want table valued function

CREATE FUNCTION fnBillParticulars()
RETURNS @BillParticulars TABLE 
(
   Id       int,
   -- other columns here
) 
AS
BEGIN
   INSERT INTO @BillParticulars (Id, ...) -- specify columns here
    SELECT  * 
    FROM    billParticulars 
    WHERE   Id = '2425';

   RETURN;
END;

answered May 16, 2013 at 9:50

John Woo

265k70 gold badges509 silver badges500 bronze badges

3 Comments

Freelancer Over a year ago

I know views, but Team Leader told me to get aquented with functions in SQL. Thanks for answer.

Freelancer Over a year ago

ohh, good, complecated, but came to know new things in function. Thanks

Freelancer Over a year ago

yeah. This was my first attempt for functions, will go for parameters now.

Pritesh Tayade · Accepted Answer · 2013-05-16 09:55:01Z

1

U written "where" Twice in the query.

query should be:

select * from billParticulars where Id='2425'

and use "returns" table

create function fnBillParticulars()
returns table
as
return (select * from billParticulars where Id='2425')
go

answered May 16, 2013 at 9:55

Pritesh Tayade

6404 silver badges11 bronze badges

1 Comment

Freelancer Over a year ago

Tayade dada, where chukun donada ithe type zalay, aani thank you, returns sathi.

Collectives™ on Stack Overflow

sql function not retrieving table and giving error

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related