Stored Procedure

saida chary

9y791Learn iOS Programming

How can I get a single column from the result set of stored procedure?

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Puneet Kankar9y ago
try this
Declare @T Table (firstname varchar(50),lastname varchar(50))
Insert @T   EXEC SelectResult
Select firstname from @T
+1
Rafnas T P9y ago
hi saida,
just write the single column which you wanted to return from procedure inside procedure and call his procedure
To create procedure

create procedure yourprocname
as
begin
select yourcolumnName from yourTable where yourconditions
end
0
saida chary9y ago
Hi Manas,

Thanks for your response.

But I would like to know as
I have stored procedure which returns multiple columns as result set from those I need only one column because I want to use these columns in another Stored Procedure.

Is it possible without using temp table and CTE?
0
Puneet Kankar9y ago
create procedure [SP_COUNTSTUDENT]
as
begin

//this types of demo query you write
//select column from table
//select top 1 column from table
//select count(column) from table

end
0
Manas Mohapatra9y ago
You can use simple SQL query to fetch single column value with ExecuteScalar(). Follow below code:
try
{
    SQLCON = new SqlConnection(connectionString);
    SQLCON.Open();
    SQLCommand = new SqlCommand();
    SQLCommand.CommandType = CommandType.Text;
    SQLCommand.Parameters.Add("username", SqlDbType.VarChar).Value = "saida";
    SQLCommand.CommandText = "SELECT role FROM tblUser WHERE USUsername = @username";
    Int32 USRole = (Int32) SQLCommand.ExecuteScalar();

    return USRole;

}
catch (Exception)
{
    // Log it
}
Same thing you can do through StoredProcedure. Follow below code:
// SQL Stored procedure

CREATE PROCEDURE [dbo].[sp_GetUserRole]
-- Add the parameters for the stored procedure here
    @username VARCHAR(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @USRole as int

-- Add the T-SQL statements to compute the return value here
SELECT @USRole = tblUser.USRole FROM tblUser WHERE USUsername = @username

-- Return the result of the function
RETURN @USRole

END

// C# code

string cnStr = "Server=.;Database=Sandbox;Integrated Security=sspi;";
using (SqlConnection cn = new SqlConnection(cnStr)) {
    cn.Open();
    using (SqlCommand cmd = new SqlCommand("sp_GetUserRole", cn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter returnValue = new SqlParameter("@USRole", SqlDbType.Int);
        returnValue.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(returnValue);

        cmd.ExecuteNonQuery();
        int returnvalue = (int)cmd.Parameters["@USRole"].Value;
    }
}
0