Stored Procedure with thyori and examples in sql server

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.

• 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

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

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

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()
{

DataTable allData = new DataTable();
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();

}
catch
{

connection.Close();

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