Creating stored procedures with Ado.net

[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?

 

[Sericinus hunter]

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.

 

[William \(Bill\) Vaughn]

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

 

[Patrice]

Try ; instead of GO. In the worst case you could submit each proc
individually...

 

[Andy]

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