Prob in writing function [Resolved]

Posted by Sushant under Sql Server on 7/4/2013 | Points: 10 | Views : 939 | Status : [Member] | Replies : 1
hi all

Table ExternalFeature_MST

CREATE TABLE [dbo].[ExternalFeature_MST](
[EF_Id] [int] NOT NULL,
[EF_Name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[EF_Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Table Prop_External_Features
CREATE TABLE [dbo].[Prop_External_Features](
[PropId] [int] NULL,
[PEFId] [int] NULL
) ON [PRIMARY]



I want to write function which returns all external features of particular property

Example:-
Following are external features
Corner
Park View
Road Side


The Function should return "Corner,Park View,Road Side"

I have one query for this
Select EF_Name as [EFeatures] from ExternalFeature_Mst, Prop_External_Features where PEFID=EF_Id and PropId=@id

Here @id will be paramet of function
But I am not able to write function
Plz help

Sushat


Responses

Posted by: Pandians on: 7/4/2013 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Check It Out!
CREATE FUNCTION Dbo.UFN_ExternalFeatures(@ID INT) RETURNS VARCHAR(MAX)

AS
BEGIN
DECLARE @Name VARCHAR(MAX)
SELECT @Name = COALESCE(@Name+',','') + EF_Name FROM ExternalFeature_Mst, Prop_External_Features where PEFID=EF_Id and PropId=@id
RETURN @Name
END
GO
SELECT Dbo.UFN_ExternalFeatures(1) [EFeatures]

GO


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Login to post response