Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 7B7E49552CF for ; Tue, 28 Feb 2012 04:50:23 -0400 (AST) Received: from nm6-vm3.bullet.mail.ne1.yahoo.com ([98.138.91.136]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1S2IlI-0006dD-4M for pgsql-sql@postgresql.org; Tue, 28 Feb 2012 08:50:22 +0000 Received: from [98.138.90.49] by nm6.bullet.mail.ne1.yahoo.com with NNFMP; 28 Feb 2012 08:50:05 -0000 Received: from [98.138.87.12] by tm2.bullet.mail.ne1.yahoo.com with NNFMP; 28 Feb 2012 08:50:04 -0000 Received: from [127.0.0.1] by omp1012.mail.ne1.yahoo.com with NNFMP; 28 Feb 2012 08:50:04 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 947923.18052.bm@omp1012.mail.ne1.yahoo.com Received: (qmail 49499 invoked by uid 60001); 28 Feb 2012 08:50:04 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1330419004; bh=7bptzN+jfRKy4JzFF3rz3mFEPsxI33EoLOzHzXhW1Sk=; h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=E9lIf+0cXIsHDb/PHAQnE9RGeafFwlKN4nFVwLZpKTNIQnaqkq1JNnO6ze3ImTbB86lOr9c82vl1krJ448KQBLGVg3nEBW9iSD7cBtBbCU2oq2YNsqsrFNvOg182leR/3MelkHwYDqZlg1s1XEUIMwHn5hWxPt1c08o97bxketk= 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=hFa4le1uD9tUbhyfTLvHBlZbkW9BvNoFP1VAjRdxx4dVjSipyT3+fQspUk6I2rNwm2UBG93lMtv30vpZ2Ucxb/1syXQw/DyMbYKQU4u4HGWLKm/BH7ttkVVafNIgJy3JKgreDtF14wvXnfu0qlFwu2ZCXI5hIDGOHJGrot6V/0Y=; X-YMail-OSG: H2z.rHwVM1lID3uHCZT5CgMW_vWh1uCYC7dNkwI86yn_eHj a5MNSV6oeFr8sYCkorj2TVurEP1cqA1MznKEtbvKMlMDbLMcnRbQyKalXqLs bEnMHXl43Dti.J35u9.vqBBVLtqlJL0HRIRLCA_7.pD5opusWZPmkREOdflU _nfeqhxDBDDbsn.QYPKMwNuXDQX39gYxk4e8x3QHJwyclyagrjlkHcq.CMMQ A1fiE2_ZekeegS3ruBqqMWJUkpGEuEol0za4RrwnKaK69KMmEtE9C.kdQH2_ nSmGtHFjdHXvn_Bej0.u3GnMwxhEeFEdTsIYGjqVCuyc4pP_Nvj2dx_nO0C4 UWV8jacWVsQZWFl_J_XR0AkxEQPSfXqfDbmp4mepyNPw9i4idQXkAc2rtaRm 55sVMHxJAN2lZi_.g3eADUBE3kpFzfWbrkc37YaVwtl7RFptJH62mTY6MtVW htg_A1Q-- Received: from [182.177.150.150] by web121602.mail.ne1.yahoo.com via HTTP; Tue, 28 Feb 2012 00:50:04 PST X-Mailer: YahooMailWebService/0.8.116.338427 Message-ID: <1330419004.47440.YahooMailNeo@web121602.mail.ne1.yahoo.com> Date: Tue, 28 Feb 2012 00:50:04 -0800 (PST) From: Rehan Saleem Reply-To: Rehan Saleem Subject: How to convert SQL store procedure to Postgresql function To: "pgsql-sql@postgresql.org" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="-2038480821-481649041-1330419004=:47440" X-Pg-Spam-Score: 1.5 (+) X-Archive-Number: 201202/89 X-Sequence-Number: 36382 ---2038480821-481649041-1330419004=:47440 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable hi ,=0Ahow can i convert this sql store procedure to postgresql function , = i shall be very thankful to you, as i am new to postgresql and i dont know = how to handle this kind of store procedure in postgresql=0Athanks=0A=0AALTE= RPROCEDURE [dbo].[sp_GetUserByID]=0A=A0=A0=A0=A0=A0 @UserId varchar(50), = =0A=A0=A0=A0=A0=A0 @KBId=A0 varchar(50),=0A=A0=A0=A0=A0=A0 @phone varchar(5= 0),=0A=A0=A0=A0=A0=A0 @mhr varchar(50),=0A=A0=A0=A0=A0=A0 @Distance varchar= (50)=3D'',=0A=A0=A0=A0=A0=A0 @Total varchar(50) output=0AAS=0ABEGIN=0A=A0= =0A=A0=A0=A0=A0=A0 DECLARE @sql nvarchar(500);=0A=A0=0A=A0=0A=A0=A0=A0=A0= =A0 if (@Distance=3D'')=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 set @Distance = =3D 1=0A=A0=A0=A0=A0=A0 =0A=A0=A0=A0=A0=A0 set @sql =3D 'select mhr_U, begi= n_U, End_U, areaSize_U, mhr_KB, begin_KB,=0AEnd_KB, areaSize_KB, '=0A=A0=A0= =A0=A0=A0 set @sql +=3D ' phone, Distance from vwuserdataSites=A0=A0=A0 whe= re UserId=3D' + @UserId=A0 =0A=A0=A0=A0=A0=A0 set @sql +=3D ' and phone>=3D= ' + @phone =0A=A0=A0=A0=A0=A0 set @sql +=3D ' AND KBId=3D' + @KBId =A0=A0 = =0A=A0=A0=A0=A0=A0 if @mhr<>'All' and ISNULL(@mhr,'')<>''=0A=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0 set @sql +=3D ' AND mhr_U=3D''' + @mhr=A0 +''''=0A=A0=A0= =A0=A0=A0 if (@Distance<>'') =0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 set @sql = +=3D ' AND (Distance<=3D' + Distance + ' or ' + Distance + '=3D1) '=0A=A0= =A0=A0=A0=A0 set @sql +=3D ' Order by=A0 mhr_U, begin_U'=0A=A0=0A=A0=A0=A0= =A0=A0 exec(@sql)=0A=A0=A0=A0=A0=A0 set @Total =3D @@ROWCOUNT=0AEND=0A=A0= =0A=A0=0A=A0=0AGO ---2038480821-481649041-1330419004=:47440 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
hi ,
how can i convert= this sql store procedure to postgresql function , i shall be very thankful= to you, as i am new to postgresql and i dont know how to handle this kind = of store procedure in postgresql
thanks

ALTER PROCEDURE=0A[dbo].[sp_GetUserByID]
=0A=0A
     = @UserId=0Avarchar(50),
= =0A=0A
  &n= bsp;   @KBId  varchar(50),
=0A=0A
      @phone=0Avarch= ar(50),=
=0A=0A
      @mhr=0Avarchar(50),
=0A=0A
      @Distance=0Avarchar(50= )=3D'',
=0A=0A
     = @Total=0Avarchar(50) output
=0A=0A
AS
=0A=0A
BEGIN=
=0A=0A
 
=0A=0A
      DECLARE @sql nvarchar(500);<= /span>
=0A=0A
 
=0A=0A
 
= =0A=0A
  &n= bsp;   if (@Distance=3D'')=0A=0A
 &nbs= p;          set @Distance =3D 1<= /o:p>
=0A=0A
     
=0A=0A
     = set @sql =3D<= /span> 'select mhr_U, begin_U, End_U, areaSize_U,= mhr_KB, begin_KB,=0AEnd_KB, areaSize_KB, '
= =0A=0A
  &n= bsp;   set @sql +=3D ' phone, Distance from vw= userdataSites    where UserId=3D' + @UserId 
=0A=0A
      set @sql +=3D ' and phone>=3D' +=0A@phone
=0A=0A
      set @sql +=3D = ' AND KBId=3D' = + @KBId   
=0A=0A
      if @mhr<>'All' and ISNULL(= @mhr,''<= /span>)<>= ''
=0A=0A
         &n= bsp;  set @sql +=3D ' AND mhr_U=3D''' + @mhr  +''''
=0A=0A
    &nb= sp; if (@Distance<>'')
=0A= =0A
   = ;         set @sql +=3D ' AND (Distance<=3D' += =0ADistance + '= or '=0A+ Distance + '=3D1) '
=0A=0A
&nb= sp;     set @sql +=3D ' Order by&nbs= p; mhr_U, begin_U'
=0A=0A
 
= =0A=0A
  &n= bsp;   exec(@sql)=0A=0A
  = ;    set @Total =3D @@ROWCOUNT
=0A=0A
END
=0A=0A
 
= =0A=0A
 
=0A=0A
<= span style=3D"font-size:10.0pt;font-family:=0A"Courier New";color= :blue;mso-no-proof:yes"> 
=0A=0A
GO=0A=0A
<= o:p> 
=0A=0A
 
---2038480821-481649041-1330419004=:47440--