Stored Procedure

Question

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

Puneet Kankar · Answer

try this Declare @T Table (firstname varchar (50),lastname varchar (50)) Insert @T EXEC SelectResult Select firstname from @T

Rafnas T P · Answer

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

saida chary · Answer

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?

Puneet Kankar · Answer

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

Manas Mohapatra · Answer

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; } }

Stored Procedure

Answers (5)