Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 31E3B960ABC for ; Mon, 26 Mar 2012 04:49:23 -0300 (ADT) Received: from nm8-vm2.bullet.mail.ne1.yahoo.com ([98.138.90.156]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SC4g5-00015B-24 for pgsql-sql@postgresql.org; Mon, 26 Mar 2012 07:49:22 +0000 Received: from [98.138.90.55] by nm8.bullet.mail.ne1.yahoo.com with NNFMP; 26 Mar 2012 07:49:07 -0000 Received: from [98.138.87.11] by tm8.bullet.mail.ne1.yahoo.com with NNFMP; 26 Mar 2012 07:49:07 -0000 Received: from [127.0.0.1] by omp1011.mail.ne1.yahoo.com with NNFMP; 26 Mar 2012 07:49:07 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 395873.68673.bm@omp1011.mail.ne1.yahoo.com Received: (qmail 47603 invoked by uid 60001); 26 Mar 2012 07:49:07 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1332748147; bh=iXRGhev1LclBCfh0P4XrdNaIkp1PJxDjsG+n7dxQ3ic=; h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=a3Tl0yxpipNxGIy8GgEPja/IBKJS4BLUiYg9vKQTBv08RRPnuH2HR3yJrmuFM0MrwvN7UhvIzQmI83izqOPNDNpVLQDuX/ff0F8h68zhzm7KyITMH6lqf0xSdGnM8XltryCFnjs6eYDXF1A4s7zGG6GYQ/mr7/BxLtCmWWqNReM= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=MvBVePwtDgYJjDzmIir6GJq+5rhj2etu/wjANI/pTk7SYucRfrVs4cSKmDXCkfGCDwd9Ughg0PNc9NFZQ7jqa0y+LjBD+rkeqPXDBYf0PVu/bCOeDpIO4xpx4cHokqO+uQsKNzf4IG+Eh6A7LscS654TsWXk190s7/BnvqSO728=; X-YMail-OSG: dGIjkqsVM1nSv8.rP4W1KC_K1l42.58ROQ8uDcQxDPSGZzs XAxxg0C.WUYpoYRV1qxw9yaokzS1bVJvJEpgwAB6rrRpQLPZpOOvoFmVEGX8 2CsrBLCrHdKep01gEOCgu8VkaI4IKuTBiBsRQNdJYVs4KzA9GrRffjzgY8Ss FDkz1R20uElQv1PszSadcirhuziu8mJy61sKUs6xufXbAtjXGaRmctvtYZds ryQ3RzyQelHLbkc0sREri_K8j1LSp9sBHDCOLoan2YKrkh_cf3vFRQtm5Ims YfwkC9BR40.oN0DCIkUqVJ2.I2SJZLJ4vFxvU_fs6hKX_3hdH_WFUvxRXkKJ EmhyNwIiiJJKko_VowPekOpGE_C6IJYj7Mxx5JSZFBU6k52rZGH94qd4na7R vSbDzEtYv5z9ezs7dko9s5dULNKIFT4ki7AYKSciNLVJa_CemFXjF8H2m9wB i1fg7Gg-- Received: from [182.177.187.204] by web121604.mail.ne1.yahoo.com via HTTP; Mon, 26 Mar 2012 00:49:07 PDT X-Mailer: YahooMailWebService/0.8.117.340979 Message-ID: <1332748147.44735.YahooMailNeo@web121604.mail.ne1.yahoo.com> Date: Mon, 26 Mar 2012 00:49:07 -0700 (PDT) From: Rehan Saleem Reply-To: Rehan Saleem Subject: MS-SQL to PostgreSql To: "pgsql-sql@postgresql.org" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="-36511695-1373296118-1332748147=:44735" X-Pg-Spam-Score: 1.6 (+) X-Archive-Number: 201203/74 X-Sequence-Number: 36469 ---36511695-1373296118-1332748147=:44735 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable hi,=0Ai am trying to convert this mssql store procedure to postgresql funct= ion 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 postgr= esql code, =A0i shall be very thankful =A0if some one convert this procedur= e into function,=A0=0A=0AALTER PROCEDURE [dbo].[sp_GetSitesByTFID]=0A@UserD= ataDetailId varchar(50),=A0=0A@KBId =A0varchar(50),=0A@bpOverlap varchar(50= ),=0A@Chr varchar(50),=0A@CentreDistance varchar(50)=3D'',=0A@TotalMatched = varchar(50) output=0AAS=0ABEGIN=0A=0ADECLARE @sql nvarchar(500);=0A=0A=0Aif= (@CentreDistance=3D'')=0Aset @CentreDistance =3D 1=0Aset @sql =3D 'select = Chr_U, Start_U, End_U, RegionSize_U, Chr_KB, Start_KB, End_KB, RegionSize_K= B, '=0Aset @sql +=3D ' bpOverlap, CentreDistance from vwChrCompareSiteswher= e UserDataDetailId=3D' + @UserDataDetailId =A0=0Aset @sql +=3D ' and bpOver= lap>=3D' + @bpOverlap=A0=0Aset @sql +=3D ' AND KBId=3D' + @KBId =0Aif @Chr<= >'All' and ISNULL(@Chr,'')<>''=0Aset @sql +=3D ' AND Chr_U=3D''' + @Chr =A0= +''''=0Aif (@CentreDistance<>'')=A0=0Aset @sql +=3D ' AND (CentreDistance<= =3D' + @CentreDistance + ' or ' + @CentreDistance + '=3D1) '=0Aset @sql += =3D ' Order by =A0Chr_U, Start_U'=0A=0Aexec(@sql)=0Aset @TotalMatched =3D @= @ROWCOUNT=0AEND=0A ---36511695-1373296118-1332748147=:44735 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
hi,
i am trying to convert this mssql store procedure to po= stgresql 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 co= de into postgresql code,  i shall be very thankful  if some one c= onvert this procedure into function, 

<= div>
ALTER P= ROCEDURE [dbo].[sp_GetSitesByTFID]
=09@UserDataDetailId varchar(50), 
=09@KBId  varcha= r(50),
=09= @bpOverlap varchar(50),
=09@Chr varchar(50),
=09@CentreDistance varchar(50)=3D'',
<= div>=09@TotalMatched varch= ar(50) output
AS
<= font face=3D"'times new roman', 'new york', times, serif">BEGIN

=09DECLARE @sql= nvarchar(500);

=09if (@CentreDistance=3D'')
=09=09set @CentreDistance =3D 1
=09
=09set @sql =3D 'select Chr_U, Start_U, End_U, RegionSize_U= , Chr_KB, Start_KB, End_KB, RegionSize_KB, '
=09set @sql +=3D ' bpOverlap, CentreDist= ance from vwChrCompareSites=09where UserDataDetailId=3D' + @UserDataDetailId  
<= span class=3D"Apple-tab-span" style=3D"white-space:pre">=09set @sql = +=3D ' and bpOverlap>=3D' + @bpOverlap 
=09set @sql +=3D ' AND KBId=3D' + @KB= Id =09
=09if @Chr<>'All' and ISNULL(@Chr,'')<= >''
=09=09set @sql +=3D ' AND Chr_U=3D''' + @Chr  +''''
=09if (@CentreDistance<>'= ') 
=09=09set @sql +=3D ' AND (CentreDistance<=3D' + @CentreDistance + ' or '= + @CentreDistance + '=3D1) '
=09set @sql +=3D ' Order by  Chr_U, Start_U'

=09exec(@sql)
=09set @TotalMatched =3D @@ROWCOUNT
END

---36511695-1373296118-1332748147=:44735--