how to get return valued from stored procedure?

TheMSsForum.com: The Microsoft Software Forum

I have problem to get return valude from my stored
procedures.

For example, if I insert a polygon and want to get the
return primaty key genrated by @@IDENTITY, I have my
stored procedure here:

REATE PROCEDURE dbo.sp_Add_Polygon_i
@Name as varchar(30) ,
@Area as float,
@Primary_Key as int Out,
@errCode int Out
AS

declare @HostProcId as nchar(8)

INSERT INTO My_Polygon (Polygon_Name, Area)
VALUES (@Name, @Area)

Set @errCode = @@error

if (@errCode = 0)
Set @Primary_Key = @@IDENTITY
else
Set @Primary_Key = 0

GO

it works fine if I call the sp from my SQL Query Analyzer.
When I call it my my C# code, I did:

oleCommand.CommandText = "sp_Add_Polygon_i";
oleCommand.CommandType =
System.Data.CommandType.StoredProcedure;

oleCommand.Parameters.Add("@Name", strName);
oleCommand.Parameters.Add("@Area", (float)0);
oleCommand.Parameters.Add("@Primary_Key", (int)1);
oleCommand.Parameters.Add("@errCode", (int)1);

oleCommand.ExecuteNonQuery();

this inserts a record to my table. How do I get the
returned "@Primary_Key"?

oleCommand.Parameters["@Primary_Key"].Value is always 1.
should I pass a reference when I add this Parameter
("@Primary_Key")? how?

Thanks a lot.

Linda Chen
Top

Re: how to get return valued from stored procedure? by William

William
Fri Dec 19 14:37:08 CST 2003

http://www.betav.com/msdn_magazine.htm
"Linda Chen" <linda.chen@faa.gov> wrote in message
news:010f01c3c66d$cb5c13d0$a601280a@phx.gbl...
>
> I have problem to get return valude from my stored
> procedures.
>
> For example, if I insert a polygon and want to get the
> return primaty key genrated by @@IDENTITY, I have my
> stored procedure here:
>
> REATE PROCEDURE dbo.sp_Add_Polygon_i
> @Name as varchar(30) ,
> @Area as float,
> @Primary_Key as int Out,
> @errCode int Out
> AS
>
> declare @HostProcId as nchar(8)
>
> INSERT INTO My_Polygon (Polygon_Name, Area)
> VALUES (@Name, @Area)
>
> Set @errCode = @@error
>
> if (@errCode = 0)
> Set @Primary_Key = @@IDENTITY
> else
> Set @Primary_Key = 0
>
> GO
>
> it works fine if I call the sp from my SQL Query Analyzer.
> When I call it my my C# code, I did:
>
> oleCommand.CommandText = "sp_Add_Polygon_i";
> oleCommand.CommandType =
> System.Data.CommandType.StoredProcedure;
>
> oleCommand.Parameters.Add("@Name", strName);
> oleCommand.Parameters.Add("@Area", (float)0);
> oleCommand.Parameters.Add("@Primary_Key", (int)1);
> oleCommand.Parameters.Add("@errCode", (int)1);
>
> oleCommand.ExecuteNonQuery();
>
> this inserts a record to my table. How do I get the
> returned "@Primary_Key"?
>
> oleCommand.Parameters["@Primary_Key"].Value is always 1.
> should I pass a reference when I add this Parameter
> ("@Primary_Key")? how?
>
> Thanks a lot.
>
> Linda Chen
>


Top

Re: how to get return valued from stored procedure? by Kathleen

Kathleen
Mon Dec 22 22:30:12 CST 2003

Linda,

You need to set the Primary_Key parameter object in .NET to be an output
parameter. You do this by setting the Direction property.

Kathleen

"Linda Chen" <linda.chen@faa.gov> wrote in message
news:010f01c3c66d$cb5c13d0$a601280a@phx.gbl...
>
> I have problem to get return valude from my stored
> procedures.
>
> For example, if I insert a polygon and want to get the
> return primaty key genrated by @@IDENTITY, I have my
> stored procedure here:
>
> REATE PROCEDURE dbo.sp_Add_Polygon_i
> @Name as varchar(30) ,
> @Area as float,
> @Primary_Key as int Out,
> @errCode int Out
> AS
>
> declare @HostProcId as nchar(8)
>
> INSERT INTO My_Polygon (Polygon_Name, Area)
> VALUES (@Name, @Area)
>
> Set @errCode = @@error
>
> if (@errCode = 0)
> Set @Primary_Key = @@IDENTITY
> else
> Set @Primary_Key = 0
>
> GO
>
> it works fine if I call the sp from my SQL Query Analyzer.
> When I call it my my C# code, I did:
>
> oleCommand.CommandText = "sp_Add_Polygon_i";
> oleCommand.CommandType =
> System.Data.CommandType.StoredProcedure;
>
> oleCommand.Parameters.Add("@Name", strName);
> oleCommand.Parameters.Add("@Area", (float)0);
> oleCommand.Parameters.Add("@Primary_Key", (int)1);
> oleCommand.Parameters.Add("@errCode", (int)1);
>
> oleCommand.ExecuteNonQuery();
>
> this inserts a record to my table. How do I get the
> returned "@Primary_Key"?
>
> oleCommand.Parameters["@Primary_Key"].Value is always 1.
> should I pass a reference when I add this Par6m4Ser
> ("@Primary_Key")? how?
>
> Thanks a lot.
>
> Linda Chen
>


Top