hi, i am trying to convert this mssql store procedure to postgresql function but it is not giving me the desired output ,this function is returning a table and you dont need to worry about what it is returning all i concern the body part of the function how to transform the ms-sql code into postgresql code, i shall be very thankful if some one convert this procedure into function,
ALTER PROCEDURE [dbo].[sp_GetSitesByTFID] @UserDataDetailId varchar(50), @KBId varchar(50), @bpOverlap varchar(50), @Chr varchar(50), @CentreDistance varchar(50)='', @TotalMatched varchar(50) output AS BEGIN DECLARE @sql nvarchar(500); if (@CentreDistance='') set @CentreDistance = 1 set @sql = 'select Chr_U, Start_U, End_U, RegionSize_U, Chr_KB, Start_KB, End_KB, RegionSize_KB, ' set @sql += ' bpOverlap, CentreDistance from vwChrCompareSiteswhere UserDataDetailId=' + @UserDataDetailId set @sql += ' and bpOverlap>=' + @bpOverlap set @sql += ' AND KBId=' + @KBId if @Chr<>'All' and ISNULL(@Chr,'')<>'' set @sql += ' AND Chr_U=''' + @Chr +'''' if (@CentreDistance<>'') set @sql += ' AND (CentreDistance<=' + @CentreDistance + ' or ' + @CentreDistance + '=1) ' set @sql += ' Order by Chr_U, Start_U' exec(@sql) set @TotalMatched = @@ROWCOUNT END
