> That is OK and looks clean. I want to know whether all stored procedures need to be modified to work with Firebird?

Yes and the same for triggers. Not only that the syntax is different,
triggers in MSSQL are statement-level, where as they are row-based in
Firebird.


--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/
http://www.firebirdsql.org/en/firebird-foundation/

> Rohit
>
> Go Green
> code1008@...
>
>
>
>
>
> -----Original Message-----
> From: Alan J Davies<Alan.Davies@...>
> To: firebird-support<firebird-support@yahoogroups.com>
> Sent: Fri, Jan 13, 2012 12:18 am
> Subject: Re: [firebird-support] Are SQL Server stored procedure and trigger syntaxes compatible with Firebird?
>
>
> No.
> Take a look at this example - the MS version outputs to Xml as well, but
> I think you may be familiar with that.
> The firebird equivalent is a lot "cleaner". These are live examples from
> a 2-server setup - 1 MS SQL, the other FB.
>
>
> ************************** MS SQL ***********************************
>
> USE [TCFMS]
> GO
> /****** Object: StoredProcedure [dbo].[s_insert_products] Script
> Date: 12/01/2012 18:40:50 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> -- =============================================
> -- Author: Alan Davies
> -- Create date: Feb 27, 2006
> -- Description: Adds a new instance of a product
> -- into the products table
> -- Return Vals: 0 - Product added successfully
> -- -1 - Product id already exists
> -- -2 - Product name parameter is NULL
> -- -3 - Fuel parameter is NULL
> -- -4 - Invalid defaultmoisture parameter entry
> -- -5 - Could not add moisture test to product
> --
> -- CHANGELOG:
> -- 20080521 MSH: Added "truck wash bypass"
> -- =============================================
> ALTER PROCEDURE [dbo].[s_insert_products]
>
> @id SMALLINT,
> @name NVARCHAR(64),
> @fuel BIT,
> @bypasstruckwash BIT,
> @defaultmoisture DECIMAL(5,2),
> @description NVARCHAR(max) = null
> AS
> BEGIN
>
> declare @rows_count INT
> declare @paramlist XML
> declare @originaldata XML
> declare @return_value int
> declare @params varchar(MAX)
> declare @transtype_number INT
> declare @events_return_value INT
>
> SET @transtype_number = 19
> SET @return_value = 0
>
> --create xml file of the original data that is being modified
> SET @originaldata = NULL
>
> SELECT @rows_count = COUNT(*)
> FROM products
> WHERE id = @id
>
> --return -1 if product id already exists
> IF @rows_count> 0
> BEGIN
> SET @return_value = -1
> GOTO log_event
> END
>
> --return -2 if name of product is not specified
> IF @name is NULL
> BEGIN
> SET @return_value = -2
> GOTO log_event
> END
>
> --return -3 if not specified whether product is a
> --biomass product
> IF @fuel is NULL
> BEGIN
> SET @return_value = -3
> GOTO log_event
> END
>
> --return -4 if default moisture of product is not valid
> IF @defaultmoisture IS NULL OR
> @defaultmoisture> 100 OR
> @defaultmoisture< 0
> BEGIN
> SET @return_value = -4
> GOTO log_event
> END
>
>
>
> INSERT INTO products (
> id, "name", fuel, bypasstruckwash, defaultmoisture, description
> ) VALUES (
> @id, @name, @fuel, @bypasstruckwash, @defaultmoisture, @description
> )
>
> IF @fuel = 1
> BEGIN
> EXEC @return_value = [dbo].[s_insert_products_tests]
> @product_id = @id,
> @test_id = 1,
> @min_value = 0,
> @max_value = 100
>
>
> IF @return_value< 0
> SET @return_value = -5
> END
>
> log_event:
>
> SET CONCAT_NULL_YIELDS_NULL OFF
>
> --create xml file of stored procedure parameters
> SET @params = '<s_insert_products>'+
> '<id>'+CONVERT(VARCHAR(64),@id)+'</id>'+
> '<name>'+@name+'</name>'+
> '<fuel>'+CONVERT(VARCHAR(64),@fuel)+'</fuel>'+
>
> '<bypasstruckwash>'+CONVERT(VARCHAR(64),@bypasstruckwash)+'</bypasstruckwash>'+
>
> '<defaultmoisture>'+CONVERT(VARCHAR(64),@defaultmoisture)+'</defaultmoisture>'+
> '<description>'+@description+'</description>'+
> '<return_value>'+CONVERT(VARCHAR(64),@return_value)+'</return_value>'+
> '</s_insert_products>'
> SET @paramlist = convert(xml, @params)
>
> SET CONCAT_NULL_YIELDS_NULL ON
>
> --insert new instance of a transaction event
> EXEC @events_return_value = [dbo].[s_insert_transevents]
> @transtype_id = @transtype_number,
> @paramlist = @paramlist,
> @originaldata = @originaldata
>
> RETURN @return_value
> END
>
> ****************************** Firebird ********************
>
> create or alter procedure MAINTAIN_PRODUCTS (
> CODE integer,
> NAME char(40),
> DEFAULTMOISTURE numeric(9,2),
> FUELCODE integer,
> TRANSPORTCODE integer,
> BYPASSTRUCKWASH char(1),
> FROMSTOCKPILE char(1),
> TOSTOCKPILE char(1),
> WEIGHACTION integer,
> ACTIONTYPE integer)
> as
> begin
> if (ActionType=1) then /* Update */
> begin
> update Product
> set Prod_Name=:Name,default_moisture=:defaultmoisture,
> transport_code=:transportcode,fuel_code=:fuelcode,
> bypass_truckwash=:bypasstruckwash,
> from_stockpile=:fromstockpile,
> to_stockpile=:tostockpile
> where Prod_Code=:Code;
> if (weighaction=1) then /* update weigh if codes change */
> begin
> update weigh
> set transport_code=:transportcode,fuel_code=:fuelcode
> where Prod_Code=:Code;
> end
> end
> else if (ActionType=2) then /* Insert */
> begin
> insert into Product(Prod_Code,Prod_Name,default_moisture,
> fuel_code,transport_code,bypass_truckwash,
> from_stockpile,to_stockpile)
> values (:Code,:Name,:defaultmoisture,
> :fuelcode,:transportcode,:bypasstruckwash,
> :fromstockpile,:tostockpile);
> end
> else if (ActionType=3) then /* Delete */
> begin
> delete from Product
> where Prod_Code=:Code;
> end
> when SQLCode -803 Do
> Exception insertException;/* Already On File */
> when SQLCode -530 Do
> Exception delete_Exception;/* Deliveries On Weigh File */
> end
>
>
>
>
>
>
>
> Alan J Davies
> Aldis
> +44 (0) 1926 842069
> +44 (0) 7885 372793
>
> On 12/01/2012 18:19, Go Green wrote:
>>
>> I want to migrate an existing SQL Server 2208 database to Firebird. It
>> contains Stored Procedures and Triggers. Firstly, I want to know whether
>> Firebird supports Stored Procedures and Triggers and secondly, are the
>> syntaxes same?
>>
>> Go Green
>> code1008@...<mailto:code1008%40aol.com>
>>
>> [Non-text portions of this message have been removed]
>>
>>
>>
>> No virus found in this message.
>> Checked by AVG - www.avg.com<http://www.avg.com>
>> Version: 2012.0.1901 / Virus Database: 2109/4738 - Release Date: 01/12/12
>>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>