Creating stored procedures with Ado.net

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Hi all,

I'm trying to write code which will create a stored procedure in the
database. The code uses a command object to execute the following sql:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE apPersonDelete
@PersonId int,
@AuditUserId int
AS

declare @result bit

set @result = -1

delete
from [Person]
where PersonId = @PersonId

if @@error = 0 begin
set @result = 0
end

return @result
go

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO


CREATE PROCEDURE apPersonUpdate
@PersonId int,
@FirstName varchar(50),
@LastName varchar(50),
@BirthDate datetime,
@Sex char(1),
@Weight decimal,
@SecurityId uniqueidentifier,
@AuditUserId int
AS

declare @result bit

set @result = -1

update [Person]
set FirstName = @FirstName,
LastName = @LastName,
BirthDate = @BirthDate,
Sex = @Sex,
Weight = @Weight,
SecurityId = @SecurityId
where PersonId = @PersonId

if @@error = 0 begin
set @result = 0
end

return @result
go

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE apPersonInsert
@FirstName varchar(50),
@LastName varchar(50),
@BirthDate datetime,
@Sex char(1),
@Weight decimal,
@SecurityId uniqueidentifier,
@PersonId int output,
@AuditUserId int
AS

declare @result bit

set @result = -1

insert into [Person]( FirstName, LastName, BirthDate, Sex, Weight,
SecurityId )

values ( @FirstName, @LastName, @BirthDate, @Sex, @Weight, @SecurityId
)


if @@error = 0 begin

set @PersonId = scope_identity()
set @result = 0
end

return @result
go

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


It runs fine in query analyzer, but when i try it via the command
object, i get errors about @result already being declared and syntax
error near create procedure.

Any ideas?
 
Andy,

GO is not part of SQL, it is used by Microsoft utilities bundled
with SQL Server, so get rid of it.

As to @result already being declared, not sure but I would try
to put begin/end around the bodies of your procedures or just
do them one by one.
 
Right. The GO is a script separator. These cannot simply be removed. A batch
has several scripts therein and each must be run separately. Creating an
batch reader/executor is a classroom exercise. It's not that hard. Scan for
the GO (<CR><LF>GO<CR><LF>) and execute the selected script.
hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Thanks for all the suggestions... sending the sql for the procs one at
a time seemed to do the trick. I added the Gos when I first started
getting errors; they do work with classic ADO IIRC.

Andy
 
Back
Top