Creating Procedure in SQL script through command object in VB.net

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I have a huge SQL script(generated through SQL server enterprise manager)
which creates tables, users and procedures. I am using a command object to
run it. It works all fine except for the procedures. The error it gives is :

" Must declare the variable @vchProperty"

The code of my stored procedure works just fine in SQL query analyser.
Code is :

create procedure dbo.dt_adduserobject_vcs
@vchProperty varchar(64)
as

set nocount on

declare @iReturn int
/*
** Create the user object if it does not exist already
*/
begin transaction
select @iReturn = objectid from dbo.dtproperties where property =
@vchProperty
if @iReturn IS NULL
begin
insert dbo.dtproperties (property) VALUES (@vchProperty)
update dbo.dtproperties set objectid=@@identity
where id=@@identity and property=@vchProperty
select @iReturn = @@identity
end
commit
return @iReturn

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

GRANT EXECUTE ON [dbo].[dt_adduserobject_vcs] TO [public]

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

Thnx
 
I'm not sure why you're receiving the error you are, but one thing you
should do in the body of your sproc is only call the @@identity
function once immediately after the insert statement and retrieve the
value into a local variable, which you can then use in the update
statement. Also, use scope_identity, not @@identity so that you get
the identity column value from that scope, and not from a trigger,
etc. See SQL BOL for more information. You'll also want to put some
error handling in the sproc to see if the insert succeeded before you
execute the update (check @@rowcount and @@error).

--Mary
 
Back
Top