Convert Informix to SQL Server

Hello, i need to convert this store procedure from Informix to SQL
Server and only obtain error's. Can anyone help me?
CREATE PROCEDURE atribui_num_carta
@.dia_env INT,
@.mes_env INT,
@.ano_env INT
DECLARE @.aux_numcarta INT,
@.aux_numcarta1 INT,
@.var_medant LIKE mudmed.medant,
@.var_cartatipo LIKE mudmed.cartatipo,
@.var_ramo LIKE mudmed.ramo,
@.var_apolice LIKE mudmed.apolice,
@.var_dataalter LIKE mudmed.dataalter,
@.var_registo LIKE mudmed.registo,
@.aux_medant LIKE mudmed.medant,
@.aux_cartatipo LIKE mudmed.cartatipo,
@.aux_ramo LIKE mudmed.ramo,
@.aux_apolice LIKE mudmed.apolice,
@.aux_dataenvio LIKE mudmed.dataenvio,
@.aux_dataenvio_comp LIKE mudmed.dataenvio_comp,
@.ja_esta_em_trans INT,
@.sql_error_num INT,
@.isam_error_num INT,
@.error_msg VARCHAR(100)
--ON EXCEPTION
-- SET sql_error_num, isam_error_num, error_msg
IF ja_esta_em_trans <> 1
ROLLBACK WORK
RAISE EXCEPTION sql_error_num, isam_error_num, error_msg;
END EXCEPTION;
LET ja_esta_em_trans = 0;
BEGIN
ON EXCEPTION IN (-535)
LET ja_esta_em_trans = 1;
END EXCEPTION;
BEGIN WORK;
END
SELECT Max(M1.NUMCARTA) INTO aux_numcarta
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
FROM mudmedhist_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
IF aux_numcarta IS NULL
LET aux_numcarta = 0
IF aux_numcarta1 IS NULL
LET aux_numcarta1 = 0
IF aux_numcarta1 > aux_numcarta
LET aux_numcarta = aux_numcarta1
END IF
LET @.aux_medant = -1;
LET @.aux_cartatipo = -1;
LET @.aux_ramo = -1;
LET @.aux_apolice = -1;
LET @.aux_numcarta1 = -1;
LET aux_dataenvio_comp = CURRENT;
FOREACH
SELECT medant, cartatipo, ramo, apolice, dataalter, registo
INTO var_medant, var_cartatipo, var_ramo, var_apolice,
var_dataalter, var_registo
FROM mudmed_grupo
WHERE (numcarta IS NULL)
ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
registo DESC
IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
OR ((var_medant>=800000 AND var_medant<=899999)
OR (var_medant>=5003000 AND var_medant<=5003500) OR
(var_medant>=5012000 AND var_medant<=5012999))
LET aux_medant = var_medant;
LET aux_cartatipo = var_cartatipo;
LET aux_numcarta1 = -1;
ELSE
IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
aux_cartatipo <> var_cartatipo
LET aux_medant = var_medant;
LET aux_cartatipo = var_cartatipo;
LET aux_numcarta = aux_numcarta + 1;
LET aux_numcarta1 = aux_numcarta;
IF aux_cartatipo == var_cartatipo AND
aux_ramo = var_ramo AND aux_apolice = var_apolice THEN
LET aux_dataenvio = NULL;
ELSE
LET aux_ramo = var_ramo;
LET aux_apolice = var_apolice;
LET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
END IF;
UPDATE mudmed
SET numcarta = aux_numcarta1,
dataenvio = aux_dataenvio,
dataenvio_comp = aux_dataenvio_comp
WHERE registo = var_registo;
END FOREACH ;
IF ja_esta_em_trans <> 1 THEN
COMMIT WORK;
END IF;
END PROCEDURE;
Thanks,
Apaxe2000If you understand t-SQL as well as the Informix's SQL dialect, it shouldn't
be hard. Instead of translating line by line, understand the overall logic
in the stored procedure and re-write using t-SQL.
Anith|||But the problem is i don't know SQL Server dialect and need to convert
this.
This is the only thing i don't know how resolve in the project i have
to do. Programming in VB is easy. Programming in SQL Server not.|||Apaxe2000 wrote:

> But the problem is i don't know SQL Server dialect and need to convert
> this.
> This is the only thing i don't know how resolve in the project i have
> to do. Programming in VB is easy. Programming in SQL Server not.
You need to spec the problem properly and work from a spec rather than
someone else's code. At least that may be what you'll have to do if you
want an answer from this forum.
The code you posted appears to be a cursor and that probably isn't the
best way to accomplish the same thing in SQL Server. But without more
information on keys, constraints and your data it's difficult to give
you a good answer.
The following article explains the best way to describe your problem
here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||It seems that you've tried converting the procedure but you are getting
errors.
Why not show us what you've done and the errors that you're getting.
You would have to be lucky to find someone here that knows both dialects and
is inclined to do all the work for you.
Besides, there are few comments in the procedure showing us what's going on
and we don't know your table structures.
@.var_medant LIKE mudmed.medant - nothing like this in SQL. You'll have to
use the parameter name @.var_medant in the procedure
LET = SET in SQL server
http://www.sommarskog.se/ for error handling in SQL server
FOREACH would be a Cursor in SQL server although you may be able to write
this without one
CURRENT = getdate()
"Apaxe2000" <Apaxe2000@.gmail.com> wrote in message
news:1135096937.302611.60280@.g14g2000cwa.googlegroups.com...
> Hello, i need to convert this store procedure from Informix to SQL
> Server and only obtain error's. Can anyone help me?
> CREATE PROCEDURE atribui_num_carta
> @.dia_env INT,
> @.mes_env INT,
> @.ano_env INT
> DECLARE @.aux_numcarta INT,
> @.aux_numcarta1 INT,
> @.var_medant LIKE mudmed.medant,
> @.var_cartatipo LIKE mudmed.cartatipo,
> @.var_ramo LIKE mudmed.ramo,
> @.var_apolice LIKE mudmed.apolice,
> @.var_dataalter LIKE mudmed.dataalter,
> @.var_registo LIKE mudmed.registo,
> @.aux_medant LIKE mudmed.medant,
> @.aux_cartatipo LIKE mudmed.cartatipo,
> @.aux_ramo LIKE mudmed.ramo,
> @.aux_apolice LIKE mudmed.apolice,
> @.aux_dataenvio LIKE mudmed.dataenvio,
> @.aux_dataenvio_comp LIKE mudmed.dataenvio_comp,
> @.ja_esta_em_trans INT,
> @.sql_error_num INT,
> @.isam_error_num INT,
> @.error_msg VARCHAR(100)
> --ON EXCEPTION
> -- SET sql_error_num, isam_error_num, error_msg
> IF ja_esta_em_trans <> 1
> ROLLBACK WORK
>
> RAISE EXCEPTION sql_error_num, isam_error_num, error_msg;
> END EXCEPTION;
> LET ja_esta_em_trans = 0;
> BEGIN
> ON EXCEPTION IN (-535)
> LET ja_esta_em_trans = 1;
> END EXCEPTION;
> BEGIN WORK;
> END
> SELECT Max(M1.NUMCARTA) INTO aux_numcarta
> FROM mudmed_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
> SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
> FROM mudmedhist_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
> IF aux_numcarta IS NULL
> LET aux_numcarta = 0
> IF aux_numcarta1 IS NULL
> LET aux_numcarta1 = 0
> IF aux_numcarta1 > aux_numcarta
> LET aux_numcarta = aux_numcarta1
> END IF
>
> LET @.aux_medant = -1;
> LET @.aux_cartatipo = -1;
> LET @.aux_ramo = -1;
> LET @.aux_apolice = -1;
> LET @.aux_numcarta1 = -1;
> LET aux_dataenvio_comp = CURRENT;
> FOREACH
> SELECT medant, cartatipo, ramo, apolice, dataalter, registo
> INTO var_medant, var_cartatipo, var_ramo, var_apolice,
> var_dataalter, var_registo
> FROM mudmed_grupo
> WHERE (numcarta IS NULL)
> ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
> registo DESC
> IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
> OR ((var_medant>=800000 AND var_medant<=899999)
> OR (var_medant>=5003000 AND var_medant<=5003500) OR
> (var_medant>=5012000 AND var_medant<=5012999))
> LET aux_medant = var_medant;
> LET aux_cartatipo = var_cartatipo;
> LET aux_numcarta1 = -1;
> ELSE
> IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
> aux_cartatipo <> var_cartatipo
> LET aux_medant = var_medant;
> LET aux_cartatipo = var_cartatipo;
> LET aux_numcarta = aux_numcarta + 1;
> LET aux_numcarta1 = aux_numcarta;
> IF aux_cartatipo == var_cartatipo AND
> aux_ramo = var_ramo AND aux_apolice = var_apolice THEN
> LET aux_dataenvio = NULL;
> ELSE
> LET aux_ramo = var_ramo;
> LET aux_apolice = var_apolice;
> LET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
> END IF;
> UPDATE mudmed
> SET numcarta = aux_numcarta1,
> dataenvio = aux_dataenvio,
> dataenvio_comp = aux_dataenvio_comp
> WHERE registo = var_registo;
> END FOREACH ;
> IF ja_esta_em_trans <> 1 THEN
> COMMIT WORK;
> END IF;
> END PROCEDURE;
> Thanks,
> Apaxe2000
>|||Hi.
This is the procedure i used:
CREATE PROCEDURE dbo.atribui_num_carta
@.dia_env INT,
@.mes_env INT,
@.ano_env INT
AS
DECLARE @.aux_numcarta INT
DECLARE @.aux_numcarta1 INT
DECLARE @.var_medant LIKE mudmed.medant
DECLARE @.var_cartatipo LIKE mudmed.cartatipo
DECLARE @.var_ramo LIKE mudmed.ramo
DECLARE @.var_apolice LIKE mudmed.apolice
DECLARE @.var_dataalter LIKE mudmed.dataalter
DECLARE @.var_registo LIKE mudmed.registo
DECLARE @.aux_medant LIKE mudmed.medant
DECLARE @.aux_cartatipo LIKE mudmed.cartatipo
DECLARE @.aux_ramo LIKE mudmed.ramo
DECLARE @.aux_apolice LIKE mudmed.apolice
DECLARE @.aux_dataenvio LIKE mudmed.dataenvio
DECLARE @.aux_dataenvio_comp LIKE mudmed.dataenvio_comp
DECLARE @.ja_esta_em_trans INT
DECLARE @.sql_error_num INT
DECLARE @.isam_error_num INT
DECLARE @.error_msg VARCHAR(100)
IF @.ja_esta_em_trans <> 1 BEGIN
ROLLBACK WORK
RAISERROR (@.sql_error_num, @.isam_error_num, @.error_msg);
END EXCEPTION;
SET @.ja_esta_em_trans = 0;
BEGIN
ON EXCEPTION IN (-535)
SET ja_esta_em_trans = 1;
END EXCEPTION;
BEGIN WORK;
END
SELECT Max(M1.NUMCARTA) INTO aux_numcarta
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
FROM mudmedhist_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
IF (aux_numcarta) IS NULL BEGIN
SET @.aux_numcarta = 0
END
IF aux_numcarta1 IS NULL BEGIN
SET @.aux_numcarta1 = 0
END
IF aux_numcarta1 > aux_numcarta
SET @.aux_numcarta = @.aux_numcarta1
END
SET aux_medant = -1;
SET aux_cartatipo = -1;
SET aux_ramo = -1;
SET aux_apolice = -1;
SET aux_numcarta1 = -1;
SET aux_dataenvio_comp = CURRENT;
FOREACH
SELECT medant, cartatipo, ramo, apolice, dataalter, registo
INTO var_medant, var_cartatipo, var_ramo, var_apolice,
var_dataalter, var_registo
FROM mudmed_grupo
WHERE (numcarta IS NULL)
ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
registo DESC
IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
OR ((var_medant>=800000 AND var_medant<=899999)
OR (var_medant>=5003000 AND var_medant<=5003500) OR
(var_medant>=5012000 AND var_medant<=5012999)) BEGIN
SET aux_medant = var_medant;
SET aux_cartatipo = var_cartatipo;
SET aux_numcarta1 = -1;
ELSE
IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
aux_cartatipo <> var_cartatipo BEGIN
SET aux_medant = var_medant;
SET aux_cartatipo = var_cartatipo;
SET aux_numcarta = aux_numcarta + 1;
SET aux_numcarta1 = aux_numcarta;
END
IF aux_cartatipo == var_cartatipo AND aux_ramo = var_ramo AND
aux_apolice = var_apolice BEGIN
SET aux_dataenvio = NULL;
ELSE
SET aux_ramo = var_ramo;
SET aux_apolice = var_apolice;
SET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
END;
UPDATE mudmed
SET numcarta = aux_numcarta1,
dataenvio = aux_dataenvio,
dataenvio_comp = aux_dataenvio_comp
WHERE registo = var_registo;
END FOREACH ;
IF ja_esta_em_trans <> 1 THEN
COMMIT WORK;
END IF;
END PROCEDURE;
I obtain this errors:
Error 156: Incorrect Syntax near the keyword 'LIKE'
Line 32: Incorrect Syntax near 'EXCEPTION'
Incorrect Syntax near the keyword 'ON'
Incorrect Syntax near the keyword 'END'
Line 75: Incorrect Syntax near ','
Line 82: Incorrect Syntax near '='
Line 87: Incorrect Syntax near '='
Line 94: Incorrect Syntax near '='
Line 95: Incorrect Syntax near '='
Line 108: Incorrect Syntax near 'FOREACH'
Incorrect Syntax near the keyword 'END'
The variable are:
LIKE mudmed.medant -> integer
LIKE mudmed.cartatipo -> smallint
LIKE mudmed.ramo -> smallint
LIKE mudmed.apolice -> smallint
LIKE mudmed.dataalter -> datetime
LIKE mudmed.registo -> integer
LIKE mudmed.dataenvio -> datetime
LIKE mudmed.dataenvio_comp -> datetime
Thanks for your help,
Apaxe2000|||Apaxe2000 (Apaxe2000@.gmail.com) writes:
> This is the procedure i used:
I don't know if you seriously expect someone to rewrite your Informix
code to SQL Server for you for free. Since I don't know Informix, I
don't know how far from Informix you have strayed, but you have quite
some way to go, before you are in SQL Server land.
If you are not interesting in learning T-SQL, and this is just a one-off,
I suggest that you try the local phonebook for consultants.
If you want to learn SQL Server, I will give you some hints.

> DECLARE @.var_medant LIKE mudmed.medant
Oracle has a similar feature, but SQL Server does not. You can only
declare a variable to be of a certain type. You can create your
own data-type alias, so that you can say:
EXEC sp_addtype mytype, 'varchar(12)'
and then you can can use mytype both in tables and for variables. But
I suspect that this does not help you here, as your tables supposedly
already exists. You will have to declare @.var_medant explicitly as
whatever type you need.

> IF @.ja_esta_em_trans <> 1 BEGIN
> ROLLBACK WORK
> RAISERROR (@.sql_error_num, @.isam_error_num, @.error_msg);
Note that if you pass a number to RAISERROR, this must be a number >= 50000,
and should have been defined with sp_addmessage. The most common is to
pass a string to RAISERROR with the error message.
The other two parameters are severity and state. Severity should be >= 11
and <= 16 and state is best set to 1.

> BEGIN
> ON EXCEPTION IN (-535)
> SET ja_esta_em_trans = 1;
> END EXCEPTION;
This syntax is not in SQL Server. You did not say which SQL Server you
are using, and this is an area where there are great difference. In SQL
2005, you can say:
BEGIN TRY
.. statements here
END TRY
BEGIN CATCH
.. error handling here
END CATCH
But in SQL 2000, you have no other choice than checking the global
variable @.@.error after each statement. Notice that this variable changes
value after each statement, so you need to put it into a local variable.
Also, note that in SQL 2000, there are errors you cannot catch at all,
since they abort the batch and rolls back the current transaction.
As for what you should check for in your error handler, I don't know,
but -535 is not an error number in SQL Server.

> SELECT Max(M1.NUMCARTA) INTO aux_numcarta
> FROM mudmed_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
I don't know what this statement does in Informnix, but in SQL Server
it creates a table, and it fails since you did not provide a name
for the column. If aux_numcarta is a variable, the syntax is
SELECT @.aux_numcarta = Max(M1.NUMCARTA)
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = @.ano_env;
Assuming that DATAENVIO is indexes, the WHERE clause is best written as
WHERE M1:DATAENVIO BETWEEN ltrim(str(@.ano_env)) + '0101' AND
ltrim(str(@.ano_env)) + '1231'
This is because, if you put an indexed column into an expresson, you
nullify the benefit of the index. (I would suspect that this applies to
Informix as well.)

> SET aux_dataenvio_comp = CURRENT;
This means nothing in SQL Server.

> FOREACH
> SELECT medant, cartatipo, ramo, apolice, dataalter, registo
> INTO var_medant, var_cartatipo, var_ramo, var_apolice,
> var_dataalter, var_registo
> FROM mudmed_grupo
> WHERE (numcarta IS NULL)
> ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
> registo DESC
I don't know what this FORACH means, but it looks like a syntax for a
cursor. But looking at the code, I can't see anything that calls for
an iteration at all. You should proably rewrite this as a single
UPDATE statement in Informix first.
The CASE expression can be handy:
SELECT @.x = CASE WHEN @.y > 10 THEN 234
WHEN @.y > 6 AND @.w > 12 THEN 123
ELSE -23
END
This is ANSI, so it should work on Informix as well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||At 15/01/06 i go to another project in Oracle and VB.NET but i need to
do this before, but i continue obtain errors and can anyone helpme only
this time?
Thanks,
Apaxe2000|||Apaxe2000 wrote:
> At 15/01/06 i go to another project in Oracle and VB.NET but i need to
> do this before, but i continue obtain errors and can anyone helpme only
> this time?
> Thanks,
> Apaxe2000
Do you really think that "continue obtain errors" is an adequate
specification of a problem? No-one's likely to be able to offer much
help without a fuller description.
I don't want to give you bad advice based on my wild guesses about the
meaning of your legacy code. That's why I asked for the extra
information I did and gave you the article explaining how to do it. You
still haven't provided any DDL, sample data or even shown us what end
result you want. If you take the time to do that you'll be pretty sure
to get more responses.
David Portas
SQL Server MVP
--