Error in writing function in sql server2005

Posted by Sushant under Sql Server on 7/3/2013 | Points: 10 | Views : 1669 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	@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

@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

Latest Posts