Prob in writing function [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts