Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Importing SQL Server Stored Procedure into PostgreSQL

Status
Not open for further replies.
Jan 4, 2005
1
IN
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top