0

I'm using the following code and SQL to try and return a value from an insert statement, but to no effect.

Can anyone help, please.

The c# is:

Int32 newProdID = 0;

SqlConnection con = new SqlConnection(GlobalVar.ConnectionStringDev);
SqlCommand cmd = new SqlCommand("sp_save_test", con);
con.Open();

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@one", one));
cmd.Parameters.Add(new SqlParameter("@two", two));
cmd.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;

cmd.Connection = con;

cmd.ExecuteNonQuery();

string id = cmd.Parameters["@id"].Value.ToString();

return newProdID;

and my SQL Server stored procedure is:

@one varchar(255),
@two varchar(255),
@id int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [dbo].[Table_2]
([One],[Two])
VALUES
(@One,
@Two)

SET @id=SCOPE_IDENTITY()

Many thanks

G

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
badgerless
  • 45
  • 8
  • Possible duplicate of [Fetch scope\_identity value in C# code from stored procedure in 3 tier architecture](https://stackoverflow.com/questions/13416811/fetch-scope-identity-value-in-c-sharp-code-from-stored-procedure-in-3-tier-archi) – Tanner Jul 24 '19 at 09:53
  • 1
    That SP definition looks incomplete. – Thom A Jul 24 '19 at 09:56
  • Please try this: SELECT @id=SCOPE_IDENTITY() return @id END – Bikram Jul 24 '19 at 10:24
  • OR simply end the sp with END – Bikram Jul 24 '19 at 10:43
  • 1
    @Bikram `RETURN` isn't for returning things like the value of `SCOPE_IDENTITY`. – Thom A Jul 24 '19 at 11:00
  • The only way of returning a value is by using a parameter which will associate procedure variable newProdID to the paramters. See output parameter : https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-2017 – jdweng Jul 24 '19 at 11:19
  • Thanks Bikram...all sorted, :) – badgerless Jul 24 '19 at 12:32

0 Answers0