SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Converting PL/SQL to T-SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

red2003xlt
Starting Member

4 Posts

Posted - 08/11/2008 :  10:52:46  Show Profile  Reply with Quote
Hello,

I'm new to SQL Server but I have a MS Access background.
Unfortunelty, I was never that much into stored procedures.

Heres my problem.

I'm trying to convert a working piece of PL/SQL into T-SQL(?)

The Oracle developer created the procedure and it works in Oracle.

The the developer (apparently) ran it thru a conversion program (AdventNet SwisSQL (Oracle To SQL Server)).

I uploaded into SQL Server, and it runs.
Doesn't affect any rows and despite have differently tables name SQL Server doesn't see to flag them (as errors).

Can anybody care to share commonsense tips?
The non-flagging of table names, What???????????

Would it help to post the code?

Edited by - red2003xlt on 08/11/2008 14:14:18

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/11/2008 :  10:54:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes, it would certainly help.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

red2003xlt
Starting Member

4 Posts

Posted - 08/11/2008 :  11:19:10  Show Profile  Reply with Quote
Here is the Modified Procedure.

Should I've posted the Orginal Procedure?
USE [master]
GO
/****** Object: StoredProcedure [dbo].[Test_Requirement_id] Script Date: 08/05/2008 18:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Test_Requirement_id]
AS

DECLARE @ls_curr_process_group VARCHAR(200)
SET @ls_curr_process_group = '''?'''

DECLARE @ln_group_row_number FLOAT
SET @ln_group_row_number = 0

DECLARE @ls_system_req1 VARCHAR(200)

DECLARE @ls_system_req2 VARCHAR(200)

DECLARE @ls_system_req3 VARCHAR(200)

DECLARE @ls_system_req4 VARCHAR(200)

DECLARE @ls_system_req5 VARCHAR(200)

DECLARE @ls_system_req6 VARCHAR(200)

DECLARE @ls_system_req7 VARCHAR(200)

DECLARE @ls_system_req8 VARCHAR(200)

DECLARE @ls_system_req9 VARCHAR(200)

DECLARE @ls_system_req10 VARCHAR(200)
declare @sys_req_1 varchar(5000)
declare @sys_req_2 varchar(5000)
declare @sys_req_3 varchar(5000)
declare @sys_req_4 varchar(5000)
declare @sys_req_5 varchar(5000)
declare @sys_req_6 varchar(5000)
declare @sys_req_7 varchar(5000)
declare @sys_req_8 varchar(5000)
declare @sys_req_9 varchar(5000)
declare @sys_req_10 varchar(5000)
declare @new_process_grp varchar(5000)
declare @PROCESS_GRP_ABV varchar(5000)

BEGIN

DECLARE cur_process_group CURSOR Global FOR
SELECT
SUBSTRING(process_grp_abv, 1, CASE dbo.ADV_CHARINDEX4('-', process_grp_abv, 1, 3)
WHEN 0 THEN LEN(PROCESS_GRP_ABV) + 1
ELSE dbo.ADV_CHARINDEX4('-', process_grp_abv, 1, 3)
END - 1) process_group_new,
a.PROCESS_GRP_ABV,
a.Sys_Req_1,
a.Sys_Req_2,
a.Sys_Req_3,
a.Sys_Req_4,
a.Sys_Req_5,
a.Sys_Req_6,
a.Sys_Req_7,
a.Sys_Req_8,
a.Sys_Req_9,
a.Sys_Req_10
FROM dbo_Process_SubProcess a (UPDLOCK)
WHERE 1 = 1
AND dbo.ADV_CHARINDEX4('-', process_grp_abv, 1, 2) > 0
AND (sys_req_1 IS NOT NULL
OR sys_req_2 IS NOT NULL
OR sys_req_3 IS NOT NULL
OR sys_req_4 IS NOT NULL
OR sys_req_5 IS NOT NULL
OR sys_req_6 IS NOT NULL
OR sys_req_7 IS NOT NULL
OR sys_req_8 IS NOT NULL
OR sys_req_9 IS NOT NULL
OR sys_req_10 IS NOT NULL)
ORDER BY process_grp_abv


OPEN cur_process_group

FETCH NEXT FROM cur_process_group INTO @new_process_grp, @PROCESS_GRP_ABV, @sys_req_1
, @sys_req_2
,@sys_req_3
, @sys_req_4
, @sys_req_5
, @sys_req_6
, @sys_req_7
, @sys_req_8
, @sys_req_9
, @sys_req_10
print @new_process_grp


WHILE (@@FETCH_STATUS <> -1)

BEGIN
IF @ls_curr_process_group != @new_process_grp -- /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ cur_process_group_rec.process_group_new
BEGIN
SELECT @ls_curr_process_group =@new_process_grp -- /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ cur_process_group_rec.process_group_new
SELECT @ln_group_row_number = 0
print 'Inside while loop'
print @ls_curr_process_group
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_1 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req1 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
print @ls_system_req1
END
ELSE
BEGIN
SELECT @ls_system_req1 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_2 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req2 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req2 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_3 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req3 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req3 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_4 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req4 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req4 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_5 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req5 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req5 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_6 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req6 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req6 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_7 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req7 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req7 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_8 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req8 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req8 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_9 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req9 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req9 = null
END

IF /* AdventNet SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ @sys_req_10 IS NOT NULL
BEGIN
SELECT @ln_group_row_number = 1 + @ln_group_row_number
SELECT @ls_system_req10 = @ls_curr_process_group + '_R' + LTRIM(RTRIM(CONVERT(VARCHAR (23), (@ln_group_row_number))))
END
ELSE
BEGIN
SELECT @ls_system_req10 = null
END

UPDATE dbo_Sys_Req_ids
SET sys_req_id_1 = @ls_system_req1,
sys_req_id_2 = @ls_system_req2,
sys_req_id_3 = @ls_system_req3,
sys_req_id_4 = @ls_system_req4,
sys_req_id_5 = @ls_system_req5,
sys_req_id_6 = @ls_system_req6,
sys_req_id_7 = @ls_system_req7,
sys_req_id_8 = @ls_system_req8,
sys_req_id_9 = @ls_system_req9,
sys_req_id_10 = @ls_system_req10,
Process_IP_Code = @PROCESS_GRP_ABV
-- WHERE CURRENT OF cur_process_group
--SELECT @adv_sql_rowcount = @@ROWCOUNT
--commit



FETCH NEXT FROM cur_process_group INTO @new_process_grp, @PROCESS_GRP_ABV, @sys_req_1
, @sys_req_2
,@sys_req_3
, @sys_req_4
, @sys_req_5
, @sys_req_6
, @sys_req_7
, @sys_req_8
, @sys_req_9
, @sys_req_10

END




CLOSE cur_process_group
DEALLOCATE cur_process_group





END

Edited by - red2003xlt on 08/11/2008 11:20:21
Go to Top of Page

red2003xlt
Starting Member

4 Posts

Posted - 08/11/2008 :  12:13:49  Show Profile  Reply with Quote
Here the Orignal Procedure; If it helps.
* Formatted on 2008/07/25 08:18 (Formatter Plus v4.8.8) */
DECLARE
ls_curr_process_group VARCHAR2 (200) := '?';
ln_group_row_number NUMBER := 0;
ls_system_req1 VARCHAR2 (200);
ls_system_req2 VARCHAR2 (200);
ls_system_req3 VARCHAR2 (200);
ls_system_req4 VARCHAR2 (200);
ls_system_req5 VARCHAR2 (200);
ls_system_req6 VARCHAR2 (200);
ls_system_req7 VARCHAR2 (200);
ls_system_req8 VARCHAR2 (200);
ls_system_req9 VARCHAR2 (200);
ls_system_req10 VARCHAR2 (200);

CURSOR cur_process_group
IS
SELECT SUBSTR (process_grp_abv,
1,
DECODE(INSTR (process_grp_abv, '-', 1, 3), 0, LENGTH(PROCESS_GRP_ABV)+1, INSTR (process_grp_abv, '-', 1, 3)) - 1
) process_group_new,
a.*
FROM qry_b201 a
WHERE 1 = 1
AND INSTR (process_grp_abv, '-', 1, 2) > 0
AND ( sys_req_1 IS NOT NULL
OR sys_req_2 IS NOT NULL
OR sys_req_3 IS NOT NULL
OR sys_req_4 IS NOT NULL
OR sys_req_5 IS NOT NULL
OR sys_req_6 IS NOT NULL
OR sys_req_7 IS NOT NULL
OR sys_req_8 IS NOT NULL
OR sys_req_9 IS NOT NULL
OR sys_req_10 IS NOT NULL
)
ORDER BY process_grp_abv
FOR UPDATE OF sys_req_id_1;
BEGIN
FOR cur_process_group_rec IN cur_process_group
LOOP
IF ls_curr_process_group != cur_process_group_rec.process_group_new
THEN
ls_curr_process_group := cur_process_group_rec.process_group_new;
ln_group_row_number := 0;
END IF;

IF cur_process_group_rec.sys_req_1 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req1 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req1 := NULL;
END IF;

IF cur_process_group_rec.sys_req_2 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req2 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req2 := NULL;
END IF;

IF cur_process_group_rec.sys_req_3 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req3 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req3 := NULL;
END IF;

IF cur_process_group_rec.sys_req_4 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req4 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req4 := NULL;
END IF;

IF cur_process_group_rec.sys_req_5 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req5 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req5 := NULL;
END IF;

IF cur_process_group_rec.sys_req_6 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req6 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req6 := NULL;
END IF;

IF cur_process_group_rec.sys_req_7 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req7 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req7 := NULL;
END IF;

IF cur_process_group_rec.sys_req_8 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req8 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req8 := NULL;
END IF;

IF cur_process_group_rec.sys_req_9 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req9 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req9 := NULL;
END IF;

IF cur_process_group_rec.sys_req_10 IS NOT NULL
THEN
ln_group_row_number := 1 + ln_group_row_number;
ls_system_req10 :=
ls_curr_process_group
|| '_R'
|| TRIM (TO_CHAR ((ln_group_row_number), '000'));
ELSE
ls_system_req10 := NULL;
END IF;

UPDATE qry_b201
SET sys_req_id_1 = ls_system_req1,
sys_req_id_2 = ls_system_req2,
sys_req_id_3 = ls_system_req3,
sys_req_id_4 = ls_system_req4,
sys_req_id_5 = ls_system_req5,
sys_req_id_6 = ls_system_req6,
sys_req_id_7 = ls_system_req7,
sys_req_id_8 = ls_system_req8,
sys_req_id_9 = ls_system_req9,
sys_req_id_10 = ls_system_req10
WHERE CURRENT OF cur_process_group;
END LOOP;

commit;
END;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000