MS SQL Server Clients - newbie, problem with running soem SQL code

Author

newbie, problem with running soem SQL code

2005-07-06, 8:23 pm

Hi all:
I am new to SQL and I am having a hard time trying to figure out the
following error:

Msg 2812, Level 16, State 62, Server PANCHO, Line 10
Could not find stored procedure 'deletej'.

My code is as follows:

*****file name: jpak.sq

use spjdatabase;
drop procedure insertj;
drop procedure deletej;

create procedure insertj @jnum varchar(5), @jname varchar(20), @jcity
varchar(20), @jInsert integer output as declare @jInSupp integer;
select @jInSupp = count (*) from J where J# = @jnum;

if (@jInSupp = 1)
begin
set @jInsert = -1;
return 0;
end;
else return -1;

insert into J values(@jnum, @jname, @jcity);

set @jInsert = 0;

create procedure deletej @jnum varchar(5), @jDelete integer output as
declare @jDelSupp integer;
select @jDelSupp = count (*) from J where J# = @jnum;

if (@jDelSupp = 1)
begin
set @jDelete = -1;
return 0;
end;
else return -1;

delete from J where J# = @jnum;

set @jDelete = 0;
go

*****file name: invokejpak.sql

use spjdatabase;
declare @eInsert integer;
declare @eDelete integer;

execute insertj 'J8', 'Wrench', 'Miami', @eInsert output;
if @eInsert = -1
print 'Insert Rejected' else
print 'Insert Accepted';

execute deletej 'J8', @eDelete output;
if @eDelete = -1
print 'Delete Rejected' else
print 'Delete Accepted';
go

*****file name: go.bat

osql -n -E -i invokejpak.sql

I run this by going to the command prompt and navigating to the
directory whwre I have these files, then just type go.exe. Any help will
be greatly appreciated.

FC

Kalen Delaney

2005-07-07, 3:24 am

Hi FC

When and how are you executing the file jpak.sq?

My guess is the error occurs when you try to drop the procedure deletej, and
the procedure doesn't exist yet.

You can make the drop conditional, by checking to see if the procedure
exists before dropping it.

You also need to break this script into batches. The definition of a
procedure must be in a batch by itself. So putting GO in between the
different actions is necessary. In addition, once the code is broken into
batches, a failure of the drop command will not prevent the rest of the
script from running.

use spjdatabase;
go
drop procedure insertj;
go
drop procedure deletej;
go

create procedure insertj @jnum varchar(5), @jname varchar(20), @jcity
varchar(20), @jInsert integer output as declare @jInSupp integer;
select @jInSupp = count (*) from J where J# = @jnum;

if (@jInSupp = 1)
begin
set @jInsert = -1;
return 0;
end;
else return -1;

insert into J values(@jnum, @jname, @jcity);

set @jInsert = 0;

go

create procedure deletej @jnum varchar(5), @jDelete integer output as
declare @jDelSupp integer;
select @jDelSupp = count (*) from J where J# = @jnum;

if (@jDelSupp = 1)
begin
set @jDelete = -1;
return 0;
end;
else return -1;

delete from J where J# = @jnum;

set @jDelete = 0;
go

Please read about CREATE PROCEDURE, DROP PROCEDURE and 'batches' in the
Books Online.
--
HTH
----------------
Kalen Delaney
SQL Server MVP
www. SolidQualityLearning
.com

"FC" < fcarpio@NOJUNKcomcas
t.net> wrote in message
news:N_qdneceVLe9FlH
fRVn-3A@comcast.com...
> Hi all:
> I am new to SQL and I am having a hard time trying to figure out the
> following error:
>
> Msg 2812, Level 16, State 62, Server PANCHO, Line 10
> Could not find stored procedure 'deletej'.
>
> My code is as follows:
>
> *****file name: jpak.sq
>
> use spjdatabase;
> drop procedure insertj;
> drop procedure deletej;
>
> create procedure insertj @jnum varchar(5), @jname varchar(20), @jcity
> varchar(20), @jInsert integer output as declare @jInSupp integer;
> select @jInSupp = count (*) from J where J# = @jnum;
>
> if (@jInSupp = 1)
> begin
> set @jInsert = -1;
> return 0;
> end;
> else return -1;
>
> insert into J values(@jnum, @jname, @jcity);
>
> set @jInsert = 0;
>
> create procedure deletej @jnum varchar(5), @jDelete integer output as
> declare @jDelSupp integer;
> select @jDelSupp = count (*) from J where J# = @jnum;
>
> if (@jDelSupp = 1)
> begin
> set @jDelete = -1;
> return 0;
> end;
> else return -1;
>
> delete from J where J# = @jnum;
>
> set @jDelete = 0;
> go
>
> *****file name: invokejpak.sql
>
> use spjdatabase;
> declare @eInsert integer;
> declare @eDelete integer;
>
> execute insertj 'J8', 'Wrench', 'Miami', @eInsert output;
> if @eInsert = -1
> print 'Insert Rejected' else
> print 'Insert Accepted';
>
> execute deletej 'J8', @eDelete output;
> if @eDelete = -1
> print 'Delete Rejected' else
> print 'Delete Accepted';
> go
>
> *****file name: go.bat
>
> osql -n -E -i invokejpak.sql
>
> I run this by going to the command prompt and navigating to the directory
> whwre I have these files, then just type go.exe. Any help will be greatly
> appreciated.
>
> FC

Sponsored Links