• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1023
  • Last Modified:

PL/SQL Error in MS SQL Server

I'm just getting my feet wet with PL/SQL in the MS SQL Server enviroment and have been strapped for figuring out why I can't get any PL/SQL scripts to run.

I have a table named Clients with numerous fields but one named clientID and in learning have generated the following PL/SQL script from a few tutorials I've flipped through.

DECLARE
      CURSOR c1 IS
            SELECT * FROM Clients;
Begin
  For rec In c1 Loop
      IF rec.clientID = '500' THEN
            dbms_output.put(rec.clientID);
    END IF;
  End Loop;
End;

From everything I've read thus far it should work and output 500 for as many clientID's that exist with the clientID of 500....

This is the error I get.


Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CURSOR'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'For'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'THEN'.

I've even tried to dumb it down to:


DECLARE
      a := '500';
Begin
  SELECT * FROM Clients WHERE clientID = a;
End

Which still does not execute, even when defining it with VARChAR or another datatype and := '500' it throws a "VARCHAR is not a defined CURSOR" or something like that... with the a := '500' PL/SQL script I get the following:

Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ':'.


I'm just trying to learn PL/SQL in this enviroment before I actually have to start using it @ my current job.  Any help will be greatly appreciated.


Thanks,
~Aqua
0
Andrew Beers
Asked:
Andrew Beers
  • 3
  • 2
  • 2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
here is the partial code
DECLARE c1 CURSOR  FOR
          SELECT * FROM Clients;
FETCH NEXT FROM C1 into @var1,@Var2
WHILE @@FETCH_STATUS = 0
Begin

--      IF rec.clientID = '500' THEN
--           dbms_output.put(rec.clientID);

FETCH NEXT FROM C1 into @var1,@Var2
End;
0
 
Andrew BeersAuthor Commented:
This is what I get with exactly what you posted.

Msg 137, Level 15, State 1, Line 4
Must declare the variable '@var1'.
Msg 137, Level 15, State 1, Line 11
Must declare the variable '@var1'.

Do I have to define var1 and var2?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Sorry,
How many fields are there in Clients table, You need to declare so many variables of the same type and length , before e the cursor delcaration

For eg:
 if you have 2 fields ClientID (int )and name (varchar(32)) then the code should be like

DECLARE @ClientID int
DECLARE @name varchar(32)

DECLARE c1 CURSOR  FOR
          SELECT * FROM Clients;
FETCH NEXT FROM C1 into @ClientID,@Name
WHILE @@FETCH_STATUS = 0
Begin
--      IF rec.clientID = '500' THEN           ----------- can u explain what this line is used for, i have no idea of Oracle
--           dbms_output.put(rec.clientID);

FETCH NEXT FROM C1 into @ClientID,@Name

End;
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LowfatspreadCommented:
MS SQL's procedural language is actually called T-SQL (TRANSACT SQL)

PL-SQL is normally understood to refer to ORACLE's procedural SQL language...

your code
    DECLARE
     CURSOR c1 IS
          SELECT * FROM Clients;
Begin
  For rec In c1 Loop
     IF rec.clientID = '500' THEN
          dbms_output.put(rec.clientID);
    END IF;
  End Loop;
End

is I'd guess ORACLE influenced...
T-SQL
doesn't use statement delimiters (;)

there is no FOR construct

you have
BEGIN
END
for codeblocks

there is a WHILE  statement  (then BEGIN END to delimit the scope (with leave) )

you also have IF   then Begin end else begin end statements
(but no end-ifs)
 
and of course the is no "forms" / screen output/input capability within T-SQL

you can either PRINT a set of variables...
or Select  ...

and return a record set...

HTH

ps...
don't forget to OPEN / CLOSE / DEALLOCATE
any cursor you use...
but in general MS SQL (T-SQL) performs quite slowly when cursors are involved so try and avoid them
whenever possible and process sets of information...





0
 
Andrew BeersAuthor Commented:
I came from working @ US Steel as a Developer and wrote many Oracle based PL/SQL scripts so yea... it is very oracle based..  I've been trying to figure out why my oracle scripts won't run in MS SQL Server.. That explains it however... Does MS allow a way to output T-SQL information from the Proc to the screen or do you have to functionally display it works by actually executing the script and getting the correct query.

~Aqua
0
 
LowfatspreadCommented:
you use Query analyser basically when you are developing you scripts...

there is a debug mode for it...

you can see the query plans there as well...

SQL profiler can let you see the actual consequences of your stored procedure/code


you can also use OSQL and BCP to do some simple query confirmation testing..

hth

 
0
 
Andrew BeersAuthor Commented:
Thank you for the follow up.  :-)

Your help was greatly appreciated and very helpful.

~Aqua
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now