Procedure Conversion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Procedure Conversion

Author

Topic

bhushanhegde
Starting Member

14 Posts

Posted - 2008-11-26 : 22:33:04

Hi,
Following procedure works in Oracle.
I want to convert it into SQL mServer2005.
Pls help me.
Proc is as follows:

CREATE OR REPLACE PROCEDURE FM_GetDetails(P_Type VARCHAR2, OutCur OUT APP_PKG_REFCUR.rcDataCursor)
IS
--*************************************************************************************************************
--Author : Sangeetha
--Date created : September 2008
--This Procedure gets the information based on the parameter.
--**************************************************************************************************************

BEGIN
CASE P_TYPE

WHEN 'TABLE_NAME' THEN
OPEN OUTCUR FOR
SELECT distinct TABLE_NAME
FROM md_screenmaster
ORDER BY TABLE_NAME;

WHEN 'SCHEDULEID' THEN
OPEN OUTCUR FOR
SELECT scheduleid, profile_name,file_name, userid, load_type FROM fm_feedstatus WHERE
load_start <= SYSDATE AND active_flag = 'Y' AND load_status='N' ORDER BY change_dt ASC;

WHEN 'LOAD_DETAILS' THEN
OPEN OUTCUR FOR
SELECT loadtype_id,loadtype_desc FROM fm_loadtype_lu WHERE active_flag='Y';

WHEN 'PROFILE_GROUP' THEN
OPEN OUTCUR FOR
SELECT a.profilemaster_pkey,a.groupname,a.profile_name,b.val,DECODE(a.profile_mode,'I','INSERT','U','UPDATE/INSERT','D','DOWNLOAD') "Profile Mode"
FROM (SELECT m.profilemaster_pkey ,G.GROUPNAME, m.profile_name,m.profile_mode FROM
FM_PROFILEMAST m,fm_profilegroup G
WHERE G.GROUPID=m.groupid) a,
(SELECT m.profilemaster_pkey , c.val val FROM fm_ldr_syntx_dtls dts,fm_profile_cfile c,
FM_PROFILEMAST m WHERE dts.parm_stmt_syntx='INFILE ' AND dts.syntx_dtls_pkey=c.ldr_syntx_dtls_pkey
AND c.profilemaster_pkey =m.profilemaster_pkey) b
WHERE a.profilemaster_pkey = b.profilemaster_pkey(+)
order by upper(a.groupname);

WHEN 'PROCESSOR' THEN
OPEN OUTCUR FOR
SELECT E.CODE, E.DESCRIPTION FROM
GENERAL_LU E
WHERE E.ACTIVE_FLAG='Y'
AND E.LOOKUP_GRP=5
ORDER BY E.CODE;

WHEN 'GROUP_DETAILS' THEN
OPEN OUTCUR FOR --DISTINCT
SELECT g.groupid GroupId,lpad(' ',5*(level-1),'-') || groupname groupname,
g.parent_groupid,g.root_groupid FROM fm_profilegroup g
WHERE Active_Flag='Y' START WITH parent_groupid=0 CONNECT BY PRIOR groupid=parent_groupid ;
--ORDER BY root_groupid,groupid;

WHEN 'GET_GROUP' THEN
OPEN OUTCUR FOR
SELECT DISTINCT g.groupid GroupId, groupname,
g.parent_groupid,g.root_groupid FROM fm_profilegroup g
WHERE Active_Flag='Y'ORDER BY root_groupid,groupid;

WHEN 'GROUP_PROFILE' THEN
OPEN OUTCUR FOR
SELECT t.groupid groupid,t.profilemaster_pkey,t.profile_name,g.parent_groupid,g.root_groupid,t.profile_mode
FROM fm_profilegroup g,fm_profilemast t
WHERE g.groupid =t.groupid ORDER BY t.profile_name;

WHEN 'DELIMETER' THEN
OPEN OUTCUR FOR
SELECT E.CODE, E.DESCRIPTION
FROM GENERAL_LU E
WHERE E.ACTIVE_FLAG='Y'
AND E.LOOKUP_GRP=4
ORDER BY E.CODE DESC;

WHEN 'TOOLTIP' THEN
OPEN OUTCUR FOR
SELECT E.CODE, E.DESCRIPTION
FROM GENERAL_LU E
WHERE E.ACTIVE_FLAG='Y'
AND E.LOOKUP_GRP=6
ORDER BY E.CODE ;

WHEN 'DEFAULT_TYPE' THEN
OPEN OUTCUR FOR
SELECT code AS key FROM md_default_lu;

WHEN 'FEED_DETAILS' THEN
OPEN OUTCUR FOR
SELECT F.PROFILE_ID,F.scheduleid, P.profile_name,F.file_name, F.userid, F.load_type,
P.FILE_TYPE,P.DELIMITER,P.PRE_PROCESSOR,P.profile_type,
P.POST_PROCESSOR,P.PRE_PROCESSOR_TYPE,P.POST_PROCESSOR_TYPE,P.PROFILE_MODE,P.File_Mode
FROM fm_feedstatus F, FM_PROFILEMAST P
WHERE p.profilemaster_pkey=F.PROFILE_ID AND F.load_start <= SYSDATE
AND F.active_flag = 'Y' AND F.load_status='N'
ORDER BY F.change_dt ASC;
-- remove start
WHEN 'DATABASEKEY' THEN
OPEN OUTCUR FOR
SELECT value FROM app_applicationconfig
WHERE parameter='DATABASEKEY';

WHEN 'FOLDER_PATH' THEN
OPEN OUTCUR FOR
SELECT parameter,value FROM APP_APPLICATIONCONFIG
WHERE parameter in ( 'DC_DX_USER_UPLOAD_PATH','FILE_EXTENSIONS_FEEDMANAGER');

WHEN 'FILE_SIZE' THEN
OPEN OUTCUR FOR
SELECT VALUE FROM app_applicationconfig WHERE parameter='FILE_SIZE';
WHEN 'ENCLOSED_BY' THEN
OPEN OUTCUR FOR
SELECT dts.syntx_dtls_pkey value
FROM fm_ldr_syntx_dtls dts
WHERE dts.parm_stmt_syntx='ENCLOSED BY';

WHEN 'FIELD_TERMINATOR' THEN
OPEN OUTCUR FOR
SELECT dts.syntx_dtls_pkey value
FROM fm_ldr_syntx_dtls dts
WHERE dts.parm_stmt_syntx='FIELDS TERMINATED BY';
WHEN 'GET_INFILE' THEN
OPEN OUTCUR FOR
SELECT dts.syntx_dtls_pkey VALUE FROM fm_ldr_syntx_dtls dts
WHERE dts.parm_stmt_syntx='INFILE ';
-- remove end
WHEN 'OPTIONS' THEN
OPEN OUTCUR FOR
SELECT dts.parm_stmt_syntx option_type, dts.syntx_dtls_pkey value
FROM fm_ldr_syntx_dtls dts
WHERE dts.parm_stmt_syntx in ('ENCLOSED BY','FIELDS TERMINATED BY','INFILE ' ,'SKIP');

END CASE;
EXCEPTION
WHEN OTHERS THEN
SYS_DB_log_error ('FM_GETDETAILS',P_Type,SQLERRM);
RAISE_APPLICATION_ERROR(-20000, SQLERRM);
END;

Regards
bhushan

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-11-26 : 23:15:16

try with IF ELSE, some thing like

CREATE PROC dbo.FM_GetDetails
(
		@P_Type VARCHAR(1000)
)
AS
SET NOCOUNT ON
BEGIN
		IF @P_Type = 'TABLE_NAME'
		BEGIN
				SELECT distinct TABLE_NAME
				FROM md_screenmaster
				ORDER BY TABLE_NAME;
		END
		ELSE IF @P_Type = 'SCHEDULEID'
		BEGIN	
				SELECT scheduleid, profile_name,file_name, userid, load_type 
				FROM fm_feedstatus 
				WHERE load_start <= SYSDATE AND active_flag = 'Y' AND load_status='N' 
				ORDER BY change_dt ASC;
		END
		.
		.
		.
		ELSE IF @P_Type = 'OPTIONS'
		BEGIN 
				SELECT dts.parm_stmt_syntx option_type, dts.syntx_dtls_pkey value
				FROM fm_ldr_syntx_dtls dts
				WHERE dts.parm_stmt_syntx in ('ENCLOSED BY','FIELDS TERMINATED BY','INFILE ' ,'SKIP');
		END

END
SET NOCOUNT OFF

"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

bhushanhegde
Starting Member

14 Posts

Posted - 2008-11-26 : 23:35:40

Hi Peter,
is there any constarint on number IF can be used in a procedure? I mean to ask, how many IF can be nested in a procedure?

Regards
bhushan

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-11-26 : 23:51:03

FROM BOL:--
IF tests can be nested after another IF or following an ELSE. The limit to the number of nested levels depends on available memory.

"There is only one difference between a dream and an aim. <br />A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-27 : 02:31:55

You can also do without nested IF's.

CREATE PROCEDURE dbo.FM_GetDetails
(
	@P_Type VARCHAR(1000)
)
AS

SET NOCOUNT ON

IF @P_Type = 'TABLE_NAME'
	BEGIN
		SELECT distinct	TABLE_NAME
		FROM		md_screenmaster
		ORDER BY	TABLE_NAME

		RETURN
	END

IF @P_Type = 'SCHEDULEID'
	BEGIN	
		SELECT		scheduleid,
				profile_name,
				file_name,
				userid,
				load_type 
		FROM		fm_feedstatus 
		WHERE		load_start <= SYSDATE
				AND active_flag = 'Y'
				AND load_status='N' 
		ORDER BY	change_dt ASC

		RETURN
	END

IF @P_Type = 'OPTIONS'
	BEGIN 
		SELECT	parm_stmt_syntx AS option_type,
			syntx_dtls_pkey AS value
		FROM	fm_ldr_syntx_dtls
		WHERE	parm_stmt_syntx in ('ENCLOSED BY', 'FIELDS TERMINATED BY', 'INFILE ', 'SKIP')

		RETURN
	END

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources