CODEWITHSUNDEEP
c#asp.netsql-serverstored-procedures
NaveenGNK
NaveenGNK

Reputation: 93

How to return GUID/ID from stored procedure

I have a stored procedure. It should return GUID/ID, but after executing it is returning the value either 2 or -1. I want it to return the GUID/ID only. Please help me to solve this.

Here is the code

Stored procedure:

create procedure SampleAddUser1
    (@Name nvarchar(MAX),
     @MailID nvarchar(MAX),
     @MobileNumber varchar(12),
     @Password nvarchar(MAX),
     @OTPNo varchar(10),
     @OTPExp datetime,
     @UserID uniqueidentifier output)
as
    if (not exists (select * from tbl_UserDetails where MobileNumber=@MobileNumber))
    begin
        begin transaction
            set @UserID = NEWID()

            insert into tbl_UserDetails(UserID, Name, Password, MobileNumber, MailID)
            values (@UserID, @Name, @Password, @MobileNumber, @MailID)

            insert into tbl_OTPDetails(UserId, OTPNo, OTPExp)
            values (@UserID, @OTPNo, @OTPExp)

            commit transaction
end

C# code is here.....

SqlParameter sqlParam = new SqlParameter("@UserID", SqlDbType.UniqueIdentifier);
sqlParam.Direction = ParameterDirection.Output;
param[6] = sqlParam;

r = _obj.ExecuteNonquery("SampleAddUser1", CommandType.StoredProcedure, param.ToArray());

Obj.ExecuteNonQuery:

   public int ExecuteNonquery(string commandText, 
   CommandType commandType, SqlParameter[] parameters)
    {
        try
        {
            cmd = new SqlCommand(commandText,con);
            cmd.CommandType = commandType;

            foreach (SqlParameter p in parameters)
            {
                if (p.Value == null)
                {
                }
                cmd.Parameters.Add(p);
            }
            int output= cmd.ExecuteNonQuery();
            con.Close();
            return output;
        }
        catch (Exception ex)
        {
            throw new ArgumentException(ex.Message);
        }
        finally
        {
            con.Close();
        }
    }

Upvotes: 0

Views: 5321

Answers (2)

Cam Bruce
Cam Bruce

Reputation: 5689

You can't get a return value or output parameter from the return value of ExecuteNonQuery(). That will only return the row(s) affected.

To get the value of an output parameter:

var conn = new SqlConnection("my connection string");
var cmd = conn.CreateCommand();
cmd.CommandText = "dbo.MyProc";
cmd.CommandType = CommandType.StoredProcedure;

var sqlParam = new SqlParameter("@UserID",
SqlDbType.UniqueIdentifier);
sqlParam.Direction = ParameterDirection.Output; 

cmd.Parameters.Add(sqlParam);
cmd.ExecuteNonQuery();
// you can now access your output param
return (Guid)sqlParam.Value;

Upvotes: 2

dana
dana

Reputation: 18135

Should be pretty straightforward to get the .Value property of the output parameter:

SqlParameter sqlParam = new SqlParameter("@UserID", SqlDbType.UniqueIdentifier);
sqlParam.Direction = ParameterDirection.Output;
param[6] = sqlParam;
r = _obj.ExecuteNonquery("SampleAddUser1", CommandType.StoredProcedure, param.ToArray());

// get the returned parameter
Guid? userId = sqlParam.Value as Guid?;

One thing I noticed about your stored procedure is that a NEWID() is not guaranteed to be generated (in the case where there already exists a record with the same @MobileNumber in the database). In which case, better be safe and use Guid? as the return type.

Upvotes: 3

Related Questions