Creating stored procedures with Ado.net

microsoft.public.dotnet.framework.adonet

9 Nov 2005 6:11 PM

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?

9 Nov 2005 6:21 PM

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.

Andy wrote:
Show quote

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

9 Nov 2005 6:46 PM

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

Show quote

"Andy" <ajohnst***@capcitypress.com> wrote in message
news:1131559867.462240.151350@o13g2000cwo.googlegroups.com...
> 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?
>

9 Nov 2005 7:00 PM

Patrice

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

--
Patrice

Show quote

"Andy" <ajohnst***@capcitypress.com> a écrit dans le message de
news:1131559867.462240.151350@o13g2000cwo.googlegroups.com...
> 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?
>

9 Nov 2005 7:36 PM

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