Stored Procedure
A stored procedure is a set of
one or more SQL statements that are stored together in database. To create a
stored procedure use CREATE PROCEDURE statement. To use the stored procedure
you send a request for it to be executed. When server recieves the request, it
executes the stored procedure.
Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.
Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server.
There are some concepts of stored procedures.
Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.
Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server.
There are some concepts of stored procedures.
- A stored procedure is one or more SQL statements that have been compiled and stored with database. A stored procedure can be started by application code on the client.
- Stored procedure can improve database performance because the SQL statements in each procedure are only compiled and optimized the first time they are executed. In sontrast SQL statements that are sent from a client to the server have to be compiled and optimized everytime ther are executed.
- In addition to SELECT statement, a stored procedure can contain othe SQL statements such as INSERT,UPDATE,DELETE. It also contain control-of-flow language.
- A trigger is a special type of procedure that executes when rows are inserted, updated or deleted from table.
- A user defined function(UDF) is a special type of procedure that can return a value or a table.
Examples:
CREATE
PROCEDURE sp_selauthors AS
BEGIN
SELECT au_fname,au_lname,title,pub_name,pub_year
FROM tblAuthors
WHERE pub_name = 'WROX'
END
BEGIN
SELECT au_fname,au_lname,title,pub_name,pub_year
FROM tblAuthors
WHERE pub_name = 'WROX'
END
Here is an example with an
input parameter,
CREATE
PROCEDURE sp_selauthors
@publisher_name varchar(50)
AS
BEGIN
SELECT au_fname,au_lname,title,pub_name,pub_year
FROM tblAuthors
WHERE pub_name = @publisher_name
END
@publisher_name varchar(50)
AS
BEGIN
SELECT au_fname,au_lname,title,pub_name,pub_year
FROM tblAuthors
WHERE pub_name = @publisher_name
END
In the above example,
@publisher_name is the input parameter and the values will be sent by your ASP
or VB code. We will see, how to send values shortly.
Another example , an Insert statement, with two input parameter can help us to understand better.
CREATE PROCEDURE sp_InsName
@FirstName varchar(20),
@LastName varchar(30)
AS
BEGIN
INSERT INTO Names(FirstName, LastName)values(@FirstName, @LastName)
END
Another example , an Insert statement, with two input parameter can help us to understand better.
CREATE PROCEDURE sp_InsName
@FirstName varchar(20),
@LastName varchar(30)
AS
BEGIN
INSERT INTO Names(FirstName, LastName)values(@FirstName, @LastName)
END
Another example
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE spVendorByState
@VendorState varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT VendorId,VendorFName,VendorLName,VendorCity,VendorState,VendorCountry,PostedDate,VendorDescription
FROM Vendor Where VendorState = @VendorState ORDER BY PostedDate
END
How to call
nested stored procedure and save the output in Temporary Table
Well, I have to
create a procedure which calls nested procedures to reduce the work again and
store the output of the nested stored procedure in the temporary table.
Here are the few
steps taken by me to achieve this task
Suppose you have
stored procedure named sp_FirstProcedure and sp_SecondProcedure
Code for sp_
FirstProcedure
Create Procedure
sp_First
AS
BEGIN
Create Table #tempTable
( City
varchar(255),
EmployeeName varchar(255)
)
Insert into #tempTable Values (’Alabama’,’Rana’)
Insert into #tempTable Values (’Alabama’,’John’)
Insert into #tempTable Values (’Alabama’,’Richard’)
Insert into #tempTable Values (’Alabama’,’Kash’)
Select * from #TempTable
Drop Table #TempTable
END
GO
Output is :
Here is the second
stored procedure where I will call first procedure
By using these
steps.
Create Procedure
sp_SecondProcedure
AS
BEGIN
Create table #tempCityEmp
(
City
varchar(255),
EmployeeName
varchar(255)
)
Insert into
#tempCityEmp
Exec sp_First
– here you can Manuplate your temporary table like I
want to —– take only the record with Rana’s name
Select * from #tempCityEmp where EmployeeName like
‘Rana’
END
Go
Output would be
Transactions
This method can only be used
when the result set is one single row. Nevertheless, this is a method that is
sometimes overlooked. Say you have this simple stored procedure:
CREATE PROCEDURE
insert_customer @name nvarchar(50),
@address nvarchar(50),
@city nvarchar(50) AS
DECLARE @cust_id int
Declare @err int
BEGIN TRANSACTION
SELECT @cust_id = Isnull(MAX(cust_id), 0) + 1 FROM customers
INSERT customers (cust_id,
name, address, city) VALUES (@cust_id,
@name, @address, @city)
Set @err= @ERROR
If(@err=0)
begin
COMMIT TRANSACTION ;
SELECT @cust_id
end
Else
begin
ROLLBACK TRANSACTION
Select -1
end
That
is, the procedure inserts a row into a table, and returns the id for the row.
Output Parameters
Rewrite this procedure as:
CREATE PROCEDURE
insert_customer @name nvarchar(50),
@address
nvarchar(50),
@city nvarchar(50),
@cust_id int OUTPUT AS
BEGIN TRANSACTION
SELECT @cust_id =
coalesce(MAX(cust_id), 0) + 1 FROM customers (UPDLOCK)
INSERT customers (cust_id,
name, address, city) VALUES (@cust_id,
@name, @address, @city)
COMMIT TRANSACTION
You can now easily call insert_customer from
another stored procedure. Just recall that in T-SQL you need to
specify the OUTPUT keyword also in the call:
EXEC insert_customer @name,
@address, @city, @cust_id OUTPUT
Delete Stored Procedure
DROP PROCEDURE dbo.uspMyProc;
How to Call SP from ASP.Net
///
<summary>
/// Gets all columns from Table1
/// </summary>
/// <returns>DataTable of all columns in Table1</returns>
public static DataTable GetAllData()
{
/// Gets all columns from Table1
/// </summary>
/// <returns>DataTable of all columns in Table1</returns>
public static DataTable GetAllData()
{
DataTable allData = new DataTable();
SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
try
{
SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
try
{
SqlCommand cmd = new SqlCommand("GetAllData",
connection);
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(allData);
connection.Close();
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(allData);
connection.Close();
}
catch
{
catch
{
connection.Close();
}
return allData;
return allData;
}
Ex 2
protected void Button1_Click(object sender, EventArgs e){
con = new SqlConnection("server=(local); database= gaurav;uid=sa;pwd=");
cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = TextBox1.Text;
cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = TextBox2.Text;
cmd.Parameters.Add("@ConfirmPassword", SqlDbType.VarChar).Value = TextBox3.Text;
cmd.Parameters.Add("@EmailID", SqlDbType.VarChar).Value = TextBox4.Text;
cmd = new SqlCommand("submitrecord", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
No comments:
Post a Comment