From: Rehan Saleem <pk_rehan@yahoo.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: MS-SQL to PostgreSql
Date: Mon, 26 Mar 2012 00:49:07 -0700 (PDT)
Message-ID: <1332748147.44735.YahooMailNeo@web121604.mail.ne1.yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pk_rehan@yahoo.com
Subject: Re: MS-SQL to PostgreSql
In-Reply-To: <1332748147.44735.YahooMailNeo@web121604.mail.ne1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox