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]
/****** 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)
{
}
No comments:
Post a Comment