Error in writing function in sql server2005

Posted by Sushant under Sql Server on 7/3/2013 | Points: 10 | Views : 651 | Status : [Member] | Replies : 2
hi all
i am writing a function in sql server2005 it should return some set of value


create function Fun_Facility(@id int)
returns int
as
begin
declare @result table
set @result Select F_Name as [Facility] from Facility_Mst, Prop_Facilities where FID=F_Id and PropId=34
return @result
end


its giving following error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'set'.

Sushat


Responses

Posted by: Pandians on: 7/3/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Kindly clarify the following items...!

- How many rows will be returned from the SELECT statemsnt ?
- If its Morethan One rows, Are you going to insert into a Table variable ?
- If so, Why you have used return type as INT ?


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sushant, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sushant on: 7/3/2013 [Member] Starter | Points: 25

Up
0
Down
@Pandians sir
ya sir return type should not be int

my table scripts
CREATE TABLE [dbo].[Facility_MST](
[F_Id] [int] NOT NULL,
[F_Name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[F_Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Prop_Facilities](
[PropId] [int] NULL,
[FId] [int] NULL
) ON [PRIMARY]

and one table is PropertyDetails
PropId is primary key of PropertyDetails

Actually i want to show Facilities of particular property with property details
to show facilities i am calling Fun_Facility(@id int) in stored procedure


sir if u need i can giv Propertydetail table script and procedure also
thanks

Sushat

Sushant, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response