Prob in writing function & stored Procedure

Posted by Sushant under Sql Server on 7/4/2013 | Points: 10 | Views : 757 | Status : [Member] | Replies : 1
Hi all
This is my stored procedure

CREATE procedure [dbo].[sp_getparticularprop]
@propId int
as
begin
select
pd.PropId as PropId,
pd.LocId as LocId,pd.PropNature as [PropNature],
lm.LocationName as LocationName,lm.Latitude as [Latitude],lm.Longitude as [Longitude],
CASE WHEN pd.Shop=1 THEN 'Shop ' else '' END +
CASE WHEN pd.Office=1 THEN 'Office ' else '' END +
CASE WHEN pd.ATM=1 THEN 'ATM ' else '' END +
CASE WHEN pd.Godown=1 THEN 'Godown ' else '' END +
CASE WHEN pd.Basement=1 THEN 'BMT ' else '' END +
CASE WHEN pd.Hall=1 THEN 'Hall ' else '' END +
CASE WHEN pd.Cabin=1 THEN 'Cabin ' else '' END +
CASE WHEN pd.Floor=1 THEN 'Floor ' else '' END +
CASE WHEN pd.ShowRoom=1 THEN 'Showroom ' else '' END +
CASE WHEN pd.Ind_House=1 THEN 'Ind. House ' else '' END +
CASE WHEN pd.Flat_Apartment=1 and DevId>0 THEN 'Builder Flat ' else '' END +
CASE WHEN pd.Flat_Apartment=1 and DevId=0 THEN 'Flat/Apartment ' else '' END as PropType,
Pd.NoOfFloor as [NoOfFloors],
case when FL_BMT=1 then 'BMT ' else '' end +
case when FL_GF=1 then 'GF ' else '' end +
case when FL_1F=1 then '1F ' else '' end +
case when FL_Mezz=1 then 'MZN ' else '' end +
case when FL_2F=1 then '2F ' else '' end +
case when FL_3F=1 then '3F ' else '' end +
case when FL_4F=1 then '4F ' else '' end +
case when FL_5F=1 then '5F ' else '' end +
case when FL_TRR=1 then 'TRR ' else '' end as [Floors],
convert(varchar(50),pd.Total_Area) + '' + pd.postfix as [TotalArea],
pd.Address_Visible as [Address],
pd.PropFor as PropFor,
convert(varchar(50),pd.Budget) + ' ' + pd.BudgetPostFix as [Budget],
pd.Bedroom as [Bedroom],bm.BrCaption as BrCaption,
pd.Description as Description,
(select top(1) ImgUrl from PropertyImages where PropId=pd.PropId ) as ImgUrl
from
PropertyDetails as pd
left outer join LocationMaster as lm on pd.LocId=lm.LocID
left outer join BedroomMaster as bm on pd.Bedroom=bm.BrID
where pd.Flag=1 and pd.PropId=@propId
end


In this stored procedure i want to call a following function In place of Underlined CODE
plz correct function and how can i call this in above stored procedure





create function getImage(@id int)
returns varchar(100)
as
begin
declare @ImgUrl varchar(100)
if not exists(select ImgUrl from PropertyImages where PropId=31)
begin
select @ImgUrl = top(1) 'notavailable.jpg' as ImgUrl from PropertyImages
end
else
begin
select @ImgUrl = Imgurl from PropertyImages where PropId=31
end
return @ImgUrl
end

Sushat


Responses

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

Up
0
Down
sorry this line shoud be underline

I want to call fuction on following place thanks

(select top(1) ImgUrl from PropertyImages where PropId=pd.PropId ) as ImgUrl

Sushat

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

Login to post response