how to get string value from stored procedure

This article discusses how to return data from stored procedures: returning result sets (SELECT statements), using output variables and using the RETURN statement.  Each example includes client-side code (ASP.NET) and server-side code (T-SQL) to read the results.

 

Result Sets

 

Result sets are what you get when you run a simple SELECT statement inside a stored procedure. Let's suppose you want a stored procedure to return a list of all the people with a given message.  The code for the stored procedure might look like this:

 

USE [Test]

GO
/****** Object:  StoredProcedure [dbo].[GetSamples]    Script Date: 04/19/2013 11:58:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[GetSamples](@Id INT)
AS
  BEGIN
      DECLARE @Message VARCHAR(250)

      IF EXISTS(SELECT *
                FROM   sample
                WHERE  id = @Id)
        BEGIN
            SELECT *
            FROM   sample
            WHERE  id = @Id

            SET @Message='record already exist'

            SELECT @Message
            return -- ***************change
        END
      ELSE
        BEGIN
            SET @Message='record does not exist'

            SELECT @Message
            return -- ***************change
        END
  END

Web Side

If you want to write a web page that calls this stored procedure and processes the results that code you'll need to add a using clause for the SqlClient namespace.  This is needed for all the client side samples.

 string returnValue = "";
            DataTable dt = new DataTable();
            try
            {
                string strconn = ConfigurationManager.ConnectionStrings["strconn"].ConnectionString;
                using (SqlConnection SqlConn = new SqlConnection(strconn))
                {
                    using (SqlCommand SqlComm = new SqlCommand("GetSamples", SqlConn))
                    {
                        SqlComm.CommandType = CommandType.StoredProcedure;
                        SqlComm.Parameters.AddWithValue("@Id", 1);
                        SqlConn.Open();
                        string Message_ = Convert.ToString(SqlComm.ExecuteScalar()); // this will give you message
                        SqlConn.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
            }
            catch (Exception ey)
            {
            }