how to get return valued from stored procedure?

  • Thread starter Thread starter Linda Chen
  • Start date Start date
L

Linda Chen

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
 
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
 
Back
Top