How to check return procedure value from sql

Question

I have this procedure

create proc Insert_New_Emp 
    @EMP_Name varchar(50),
    @EMP_Email varchar(50),
    @EMP_Role  varchar (10),
    @Username  varchar(50),
    @password  varchar(50),
    @EMP_Phone  varchar(15),
    @EMP_Department  varchar(50),
    @Question  varchar(200)
as
begin
    insert into EMP_Info (EMP_Name, EMP_Email, EMP_Role, Username, password, 
                          EMP_Phone, EMP_Department, Question)
    values (@EMP_Name, @EMP_Email, @EMP_Role, @Username, @password,
            @EMP_Phone, @EMP_Department, @Question)

    if (exists(select EMP_Role from EMP_Info where EMP_Role ='Admin' ))
        return 'Admin'
    else
        return 'Employee'
end

I need to check the procedure code in Windows Form C#

When the return Admin do something and else do something

score 1 · Answer 1 · answered Oct 17 '14 at 04:39

A RETURN statement can return an integer code, with zero indicating success and non-zero for warning/error. It is not intended to return data. To return a scalar value back to the application, use either an OUTPUT parameter or SELECT statement. Examples below.

CREATE PROC Insert_New_Emp
    @EMP_Name varchar(50)
  , @EMP_Email varchar(50)
  , @EMP_Role varchar(10)
  , @Username varchar(50)
  , @password varchar(50)
  , @EMP_Phone varchar(15)
  , @EMP_Department varchar(50)
  , @Question varchar(200)
  , @EmpType varchar(8) OUTPUT
AS
    BEGIN
        INSERT  INTO EMP_Info
                ( EMP_Name
                , EMP_Email
                , EMP_Role
                , Username
                , password
                , EMP_Phone
                , EMP_Department
                , Question
                )
        VALUES  ( @EMP_Name
                , @EMP_Email
                , @EMP_Role
                , @Username
                , @password
                , @EMP_Phone
                , @EMP_Department
                , @Question
                );

        IF EXISTS ( SELECT    EMP_Role
                      FROM      EMP_Info
                      WHERE     EMP_Role = 'Admin' )
            SET @EmpType = 'Admin'
        ELSE
            SET @EmpType = 'Employee';
    END;
GO

CREATE PROC Insert_New_Emp
    @EMP_Name varchar(50)
  , @EMP_Email varchar(50)
  , @EMP_Role varchar(10)
  , @Username varchar(50)
  , @password varchar(50)
  , @EMP_Phone varchar(15)
  , @EMP_Department varchar(50)
  , @Question varchar(200)
AS
    BEGIN
        INSERT  INTO EMP_Info
                ( EMP_Name
                , EMP_Email
                , EMP_Role
                , Username
                , password
                , EMP_Phone
                , EMP_Department
                , Question
                )
        VALUES  ( @EMP_Name
                , @EMP_Email
                , @EMP_Role
                , @Username
                , @password
                , @EMP_Phone
                , @EMP_Department
                , @Question
                );

        IF EXISTS ( SELECT    EMP_Role
                      FROM      EMP_Info
                      WHERE     EMP_Role = 'Admin' )
            SELECT 'Admin' AS EmpType
        ELSE
            SELECT 'Employee' AS EmpType; 
    END;
GO

score 0 · Answer 2 · edited May 23 '17 at 12:05

If you want to execute storedproc from c# code then you need to do as below :

private void button1_Click(object sender, EventArgs e) {
using (SqlConnection con = new SqlConnection(dc.Con)) {
using (SqlCommand cmd = new SqlCommand("sp_Add_contact", con)) {
  cmd.CommandType = CommandType.StoredProcedure;

  cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
  cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text;

  con.Open();
  cmd.ExecuteNonQuery();
}

} }

Just replace the procedure name from 'sp_Add_contact' and add your own parameters with the data type as defined in your stored proc

Original answer : here

Rahul Singh · Accepted Answer · 2014-10-22T16:57:06.193

0

I will suggest you to modify your SP to return an integer value, because a Stored Proc can only return Integer values. Modiy it like this:-

Declare @ReturnVal INT if (exists(select EMP_Role from EMP_Info where EMP_Role ='Admin' )) SET @ReturnVal = 1 -- return 1 for Admin else SET @ReturnVal = 2 --return 2 for Employee Return @ReturnVal end

Then you can call this SP from your ADO.NET code as:-

SqlParameter returnParam = cmd.Parameters.Add("ReturnVal", SqlDbType.Int);
  returnParam.Direction = ParameterDirection.ReturnValue;
  cmd.ExecuteNonQuery();
 //Read the returned value.
  int UserType= (int) returnParam.Value;

Also, It would be better if you define an Enum in your code like this and use this in your code rather than hard-coding in SP:-

 public enum EmployeeType
    {
        Admin = 1,
        Employee = 2
    }

edited Oct 22 '14 at 16:57

answered Oct 17 '14 at 05:01

Rahul Singh

21,585
6
41
56

@Shaimaa - Just include a local variable in ur SP and return it, have updated the code. – Rahul Singh Oct 22 '14 at 16:59
returned null SqlCommand cmd = new SqlCommand("SELECT UserName,Password FROM EMP_Info WHERE UserName='" + txt_Username.Text + "' and Password='" + txt_password.Text + "'", sqlcon); cmd.CommandText = "Check_role"; cmd.CommandType = CommandType.StoredProcedure; SqlParameter returnParam = cmd.Parameters.AddWithValue("@result", SqlDbType.Int); returnParam.Direction = ParameterDirection.ReturnValue; cmd.ExecuteNonQuery(); int UserType = (int)returnParam.Value; – Shaimaa Fawzy Oct 22 '14 at 17:11
@Shaimaa - Please run sql profiler and check what return value SP is returning. Also, I would recommend not to use your select query directly since sql injection problem may occur. – Rahul Singh Oct 22 '14 at 17:24

How to check return procedure value from sql

3 Answers3