Re: converting from Oracle to SQL Server

thanx to all folks, I solved the problem by using stored procedure, That sql

statement is also to use with ASP so I am writing whole solution here.

set cnn=server.createobject("AdoDB.connection")

strC="Provider=SQLOLEDB.1;Password=[YOUR_SQL_SERVER_PASSWORD];Persist  Security
Info=True;User ID=[YOUR_SQL_SERVER_USERID];Initial

Catalog=[YOUR_SQL_SERVER_DATABASE_NAME];Data Source=[YOUR_SQL_SERVER_COMPUTER]"

cnn.open strC

'creating store procedure that you can use instead of "start
with", "connect
by" and "prior"

SQLStr1="CREATE PROCEDURE expand (_at_current char(20)) AS SET NOCOUNT ON DECLARE _at_lvl int, @line char(20) CREATE TABLE #stack (item char(20), lvl int) INSERT INTO #stack VALUES (_at_current, 1) SELECT @lvl = 1 WHILE @lvl > 0 BEGIN IF EXISTS (SELECT * FROM #stack WHERE lvl = _at_lvl) BEGIN SELECT _at_current = item FROM #stack WHERE lvl = @lvl SELECT @line = space(@lvl - 1) + _at_current PRINT @line
DELETE FROM #stack WHERE lvl = _at_lvl AND item = @current INSERT #stack SELECT LEVEL_CODE, _at_lvl + 1 FROM T_NBS_LEVELS WHERE PARENT_CODE = _at_current IF @@ROWCOUNT > 0 SELECT @lvl = @lvl + 1 END ELSE SELECT @lvl = _at_lvl - 1 END"

'executing this stored procedure in MS SQL SERVER
SQLStr2="execute expand N'0'"

'removing procedure

SQLStr3="DROP PROCEDURE expand "

'remove the procedure if it's not necessary to store it in your
database, but

if you want to keep it, just create it in MS SQL SERVER then only use execute

statement in your code. When you create it using codes and then if you do not

remove it, it will give an error since it'll try to create existing procedure

each time.

'executing in ASP

cnn.execute(sqlstr1)
cnn.execute(sqlstr2)
cnn.execute(sqlstr3)

--
Posted via http://dbforums.com