How can I retrieve this?

Question

This is my stored procedure:

BEGIN
SET NOCOUNT ON;

INSERT INTO [dbo].[KYS_CustomerComplaints] (
    CustomerId
    ,UserId
    ,STATE
    ,Address
    ,PhoneNumber
    ,Email
    ,StorageCondition
    ,OtherConditions
    ,ProductId
    ,ProductAmount
    ,LOTNo
    ,HandBill
    ,CallingBill
    ,ProductionDate
    ,ExpirationDate
    ,CreatedDate
    ,LastEditionDate
    ,LastEdited_UserId
    ,Code
    )
VALUES (
    @CustomerId
    ,@UserId
    ,@State
    ,@Address
    ,@PhoneNumber
    ,@Email
    ,@StorageCondition
    ,@OtherConditions
    ,@ProductId
    ,@ProductAmount
    ,@LOTNo
    ,@HandBill
    ,@CallingBill
    ,@ProductionDate
    ,@ExpirationDate
    ,@CreatedDate
    ,@LastEditionDate
    ,@LastEdited_UserId
    ,@Code
    )

DECLARE @new_identity INT;

SELECT @new_identity = SCOPE_IDENTITY()

RETURN @new_identity;
END

How can I give Output Id?

var parameters = new[]
{
    new SqlParameter("@CustomerId",customer.CustomerId ),
    new SqlParameter("@UserId",customer.UserId),
    new SqlParameter("@State",customer.State ),
    new SqlParameter("@Address",customer.Address ),
    new SqlParameter("@PhoneNumber",customer.PhoneNumber ),
    new SqlParameter("@Email",customer.Email ),
    new SqlParameter("@StorageCondition",customer.StorageCondition ),
    new SqlParameter("@OtherConditions",customer.OtherConditions ),
    new SqlParameter("@ProductId",customer.ProductId ),
    new SqlParameter("@ProductAmount",customer.ProductAmount ),
    new SqlParameter("@LOTNo",customer.LOTNo ),
    new SqlParameter("@HandBill",customer.HandBill ),
    new SqlParameter("@CallingBill",customer.CallingBill ),
    new SqlParameter("@ProductionDate",customer.ProductionDate ),
    new SqlParameter("@ExpirationDate",customer.ExpirationDate ),
    new SqlParameter("@CreatedDate",customer.CreatedDate ),
    new SqlParameter("@LastEditionDate",customer.LastEditionDate ),
    new SqlParameter("@LastEdited_UserId",customer.LastEdited_UserId ),
    new SqlParameter("@Code",customer.Code ),
};

var model = AdoNetHelper.ExecuteSql(ProcType.Scalar, ReturnType.DataSet, "CRM_CustomerComplaint_Add", parameters);

return "Başarıyla Eklendi";

put your insert into a stored proc, add an output parameter, and call the proc using ExecuteNonQuery. See more here: https://blogs.msdn.microsoft.com/spike/2009/01/27/sqlcommand-executenonquery-returns-1-when-doing-insert-update-delete/ — user1443098, Oct 01 '18 at 13:56
you can change your last statement to `select @new_identity` in stead of `return @new_identity` — GuidoG, Oct 01 '18 at 14:06
You'll either have modify the `AdoNetHelper` type to allow for stored procedure return values, or modify the procedure to `select` the value into a result set rather than returning it. — Joel Coehoorn, Oct 01 '18 at 14:12

GuidoG · Accepted Answer · 2018-10-01T14:45:13.347

first method: add an outputparameter like this

create procedure myStoredProc(@input1 int, ..., @NewIdentity int output)
as
begin
     set nocount on;
     insert into KYS_CustomerComplaints(...)  values (...);
     SELECT @NewIdentity = SCOPE_IDENTITY();
end

second method: let the procedure return the value like this

create procedure myStoredProc(@input1 int, ...)
as
begin
     set nocount on;
     insert into KYS_CustomerComplaints(...)  values (...);
     SELECT SCOPE_IDENTITY();
end

There is no use for a return statement in your case.

More info here
https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-2017

and here
Using stored procedure output parameters in C#

How can I retrieve this?

1 Answers1