Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    1

    Unanswered: noob: How do i run pl/sql on ms sql?

    Hi experts,

    I'm using MS SQL 2000. The closest i could get to having pl/sql within ms sql was tru its stored procedures feature.

    I created a new stored procedure within the built-in Northwind database. I pasted the following inside:

    -----------------------------------------------------------------
    declare

    vname Employees.firstname%TYPE;

    begin

    SELECT firstname INTO vname FROM Employees
    WHERE firstname = 'Nancy';

    dbms_output.put_line ('Name is ' || vname);

    EXCEPTION

    when NO_DATA_FOUND then
    dbms_output.put_line ('no data found');

    when TOO_MANY_RECORDS then
    dbms_output.put_line ('too many records');


    END;

    -----------------------------------------------------------------


    I checked the syntax and i get some error about the employees table.
    Error 155: 'Employees' is not a recognized cursor option

    Any idea?

    Thanks..

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You've got some serious reading to do....there is a major methodolgy switch you'll have to understand (not to mention sytax) between the two...

    Oracle has a lot of nice "built in" features, and at the same time is extremely painful...

    For example, ylucan not use %TYPE...

    YOu must explicitlety declare EVERYTHING...

    This is how you write what your example is trying to do...

    Code:
    CREATE PROC mySproc99
    AS
    
    BEGIN
    
    	DECLARE @vname varchar(40), @Rowcount int, @Error int
    
    	SELECT @vname = Lastname 
    	  FROM Employees
    	 WHERE firstname = 'Nancy'
    
    	SELECT @RowCount = @@ROWCOUNT, @Error = @@Error
    
    	IF @Error <> 0
    		BEGIN
    		PRINT 'Error Condition ' + CONVERT(varchar(5),@Error)
    		Return @Error
     		END
    
    	IF @RowCount <> 1
    		BEGIN
    		PRINT 'Multiple Rows Found Error'
    		Return 2
     		END
    
    	IF @RowCount = 1
    		BEGIN
    		PRINT 'Name is '+ @vname
    		Return 0
    		END
    END
    
    GO
    
    EXEC mySproc99
    GO
    
    DROP PROC mySproc99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •