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..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...

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