Importing SQL Server Stored Procedure into PostgreSQL

[ratankalwa]

Very Difficult To Import SQL Server Stored Procedure

We are in the process of importing a SQL Server database, We could transfter the data tables, but it is a huge challenge to actually rewrite all the stored procedures and they way data is returned. After browsing lots of sites and books, I did not come across how to write a stored procedure in Postgre SQL, We have to write it using a Function. Within Microsoft SQL Server, I can just write a procedure to return a resultset based on various parameters.

In POSTGRESQL I have to create a composite type that defines what columns need to be retrieved and return the data using composite type and again I have to select the data from the function. I have around 500 Stored Procedures, It is a real showstopper for my conversion.

Is there any better way to do this.

I am just posting the stored procedure in SQL Server. Can somebody help me how to achieve this in Postgre SQL.

ALTER PROC [dbo].[uspGetChildForumCategories]
(
@CompanyId int,
@SiteId int,
@DepartmentId int)

AS
BEGIN

SET NOCOUNT ON;

Set @DepartmentId = NULLIF(0, NULL)

If @DepartmentId Is Not Null
Begin
Select ForumCategoryId,
ForumCategoryName,
Count(*) Over() as RecordCount
From tblForumCategory WITH (NOLOCK)
Where ForumCategoryParentId <> 0
And SiteId = @SiteId
And CompanyId = @CompanyId
And DepartmentId = @DepartmentId
End
Else
If @DepartmentId Is Null
Begin
Select ForumCategoryId,
ForumCategoryName,
Count(*) Over() as RecordCount
From tblForumCategory WITH (NOLOCK)
Where ForumCategoryParentId <> 0
And SiteId = @SiteId
And CompanyId = @CompanyId
End


SET NOCOUNT OFF;

END

If I cannot do the above, how come Postgre SQL is so popular. Is it popular just because it is open source and free of cost.

Really Interesting.