CODEWITHSUNDEEP
c#asp.netsql-serverado.net
metalwailer
metalwailer

Reputation: 1

output parameter returns null value when I run on ADO. NET in C#

I'm sure this question has already been answered in this forum. I went through most of them and tried all the possible fixes but it did not work in my case. I'm not sure where I'm going wrong.

My stored procedure works fine in SQL Server Management Studio. It is returning the output whenever I run my stored procedure. But the output parameter returns NULL value when I call it from my ADO. NET code. I'm pretty new to this. Any help to resolve this issue is very much appreciated.

Stored Procedure :

CREATE PROCEDURE [dbo].[sp_ZebraGetEmpName]
@EmpId int,
@EmpName varchar(40) out
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT EmployeeName
    FROM  ZebraPrinter.dbo.EmployeeInfo
    WHERE EmpID = @EmpId

    RETURN (@EmpName)
END

GO

ADO .NET Code :

public string GetEmpName(int EmpId)
    {
        string connStr = ConfigurationManager.ConnectionStrings["ZebraPrinterConnectionString"].ConnectionString;

        SqlConnection con = new SqlConnection(connStr);

            SqlCommand cmd = new SqlCommand("sp_ZebraGetEmpName", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@EmpId",EmpId);

            cmd.Parameters.Add("@EmpName", SqlDbType.VarChar, 40);
            cmd.Parameters["@EmpName"].Direction = ParameterDirection.Output;                

            con.Open();
            cmd.ExecuteScalar();
            con.Close();

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


            return (EmpName);


    }

Upvotes: 0

Views: 3244

Answers (3)

A_Sk
A_Sk

Reputation: 4630

You getting the null value in the output parameter because you are not assigning the value to the output parameter.

When you are using a output parameter then you have to set it's value in the stored procedure to use it's value later.

Use:

SET @EmpName = (SELECT TOP 1 EmployeeName
                FROM ZebraPrinter.dbo.EmployeeInfo
                WHERE EmpID = @EmpId)

and your stored procedure should be like this:

CREATE PROCEDURE [dbo].[sp_ZebraGetEmpName]
    @EmpId int,
    @EmpName varchar(40) out
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SET @EmpName = (SELECT TOP 1 EmployeeName
                    FROM ZebraPrinter.dbo.EmployeeInfo
                    WHERE EmpID = @EmpId)

END

GO

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93694

You didn't assign any values to @EmpName variable in procedure so it will be NULL. Change the select query to assign value to @EmpName

Also you don't need procedure to do this. 

SELECT @EmpName=EmployeeName
FROM  ZebraPrinter.dbo.EmployeeInfo
WHERE EmpID = @EmpId

Upvotes: 2

Joe Enos
Joe Enos

Reputation: 40393

Returning something, setting an output parameter value, and selecting something are three different things.

You can set @EmpName like NoDisplayName's answer and leave your C# code as-is, or you can just run the select and pull the value when you execute the query:

CREATE PROCEDURE [dbo].[sp_ZebraGetEmpName]
@EmpId int
AS
BEGIN
    SELECT EmployeeName
    FROM  ZebraPrinter.dbo.EmployeeInfo
    WHERE EmpID = @EmpId
END

string empName = (string)cmd.ExecuteScalar();

What you're seeing in Management Studio when you run this is not the return value, but rather the query result. The return value of your query would be null.

Upvotes: 1

Related Questions