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.

Andy wrote:
> 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?
>

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

"Andy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>

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

--
Patrice

"Andy" <(E-Mail Removed)> a écrit dans le message de
news:(E-Mail Removed)...
> 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?
>

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