Stored Procedure in SQL server 2005

What is Stored Procedure?

Stored procedures are an important aspect in all database programs. A stored procedure is a set of one or more SQL statements that are stored together in database. Microsoft SQL Server provides the Stored Procedure mechanism to simplify the database development process by grouping Transact-SQL statements into manageable blocks. 

Benefits of Stored Procedures

• Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
• Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
• Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
• Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.

Creating Stored Procedures

 Syntax:

CREATE PROCEDURE  p_GetOrderByName  -- Name of stored procedure
@Name varchar(10)                                             -- input parameters of the SP
AS
SELECT Item, Quantity                  -- Query or other task you want to do
FROM Order                                   -- in stored procedure
WHERE Name = @Name 

 Executing/Calling Stored Procedures

exec p_GetOrderByName 'bharat' -- 'bharat' is the input parameter required by

                                                         --procedure

 

Order Table

OrderID						Item	Name		Quantity
142						Green beans	bharat		100
825						Corn	Ravi		140
512						Lima beans	Manoj		180
491						Tomatoes	Naseem		80
379						Watermelon	Vipin		85

result:

Item                       Quantity

Green beans           100

Cursor in sql server 2005

What Is Cursor

The typical SQL Commands operate on all row in the set at one time, but sometimes we need to manipulate data in a set on row - by - row basis, then the role of cursor comes in SQL. Cursor is a database object used to manipulate data in a set on a row-by-row basis by an application. 

The basic syntax of a cursor is:

 -- variable declaration

DECLARE @AuthorID char(11)

-- cursor declaration 

DECLARE c1 CURSOR READ_ONLY -- c1 is the cursor name

FOR

SELECT au_id  -- column name from which the values to be fetched

FROM authors -- table name on which cursor applies

OPEN c1  -- open the cursor

FETCH NEXT FROM c1  -- fetching row from table using cursor

INTO @AuthorID      -- column from which values to be get

WHILE @@FETCH_STATUS = 0 -- cheking the fetch status if 0 then row fetched 

                         -- else value is negative

BEGIN

 -- your code goes here like printing the author id got form cursor

        PRINT @AuthorID

        FETCH NEXT FROM c1 -- fetching next row from table using cursor

        INTO @AuthorID

END

CLOSE c1           -- closing cursor

DEALLOCATE c1      -- deallocating cursor from memory

Joins in sql server 2005

Joins in sql server 2005

What is Join?

A join is used to combine columns from two or more tables into a single result set. To join data from two tables you write the names of two tables in the FROM clause along with JOIN keyword and an ON phrase that specifies the join condition. The join condition indicates how two tables should be compared. In most cases they are compares on the base on the relationship of primary key of the first table and foreign key of the second table.I will tell you about the following types of joins in that article.

Join Types

You can do two types of Join: ‘Inner’ Join or an ‘Outer’ Join.

Inner Join:

This will only return rows when there is at least one row in both tables that match the join condition.

Syntax:

SELECT * FROM table_name1

INNER JOIN table_name2

ON table_name1.column_name = table_name2.column_name

Example Inner Join Statement

SELECT * FROM Individual

INNER JOIN Publisher

ON Individual.IndividualId = Publisher.IndividualId

Note: We could use table aliases instead of the full table name. This will keep our statement shorter. For example:

SELECT * FROM Individual AS Ind

INNER JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId

Outer Join:

• LEFT OUTER JOIN (or LEFT JOIN): This will return rows that have data in the left table (left of the JOIN keyword), even if there's no matching rows in the right table.
• RIGHT OUTER JOIN (or RIGHT JOIN): This will return rows that have data in the right table (right of the JOIN keyword), even if there's no matching rows in the left table.
• FULL OUTER JOIN (or FULL JOIN): This will return all rows, as long as there's matching data in one of the tables.

Syntax:

Left Join:

SELECT * FROM table_name1

LEFT JOIN table_name2

ON table_name1.column_name = table_name2.column_name

Right Join:

SELECT * FROM table_name1

RIGHT JOIN table_name2

ON table_name1.column_name = table_name2.column_name

Full Join:

SELECT * FROM table_name1

FULL JOIN table_name2

ON table_name1.column_name = table_name2.column_name

Left Outer Join

Use this when you only want to return rows that have matching data in the left table, even if there's no matching rows in the right table.

Example SQL statement

SELECT * FROM Individual AS Ind

LEFT JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table

Id	FirstName	UserName
1	Bharat	bharat
2	Rahul	Rahul
3	Piyush	Piyush
4	Sandy	sandy

Right Table

IndividualId	AccessLevel
1	Programmer
2	Tester
3	Sales Executive
10	Manager

Result

IndividualId	FirstName	UserName		IndividualId	AccessLevel
1	Bharat	Bharat		1	Programmer
2	Rahul	Rahul		2	tester
3	Piyush	Piyush		3	SalesExecutive
4	Sandy	sandy		Null	null

Right Outer Join

Use this when you only want to return rows that have matching data in the right table, even if there's no matching rows in the left table.

Example SQL statement

SELECT * FROM Individual AS Ind

RIGHT JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table

Id	FirstName	UserName
1	Bharat	bharat
2	Rahul	Rahul
3	Piyush	Piyush
4	Sandy	sandy

Right Table

IndividualId	AccessLevel
1	Programmer
2	Tester
3	Sales Executive
10	Manager

Result

IndividualId FirstName UserName IndividualId AccessLevel 1 Bharat Bharat 1 Programmer 2 Rahul Rahul 2 tester 3 Piyush Piyush 3 SalesExecutive Null Null null 10 Manager

Full Outer Join

Use this when you want to all rows, even if there's no matching rows in the right table.

Example SQL statement

SELECT * FROM Individual AS Ind

FULL JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table

Id	FirstName	UserName
1	Bharat	bharat
2	Rahul	Rahul
3	Piyush	Piyush
4	Sandy	sandy

Right Table

IndividualId	AccessLevel
1	Programmer
2	Tester
3	Sales Executive
10	Manager

Result

IndividualId FirstName UserName IndividualId AccessLevel 1 Bharat Bharat 1 Programmer 2 Rahul Rahul 2 tester 3 Piyush Piyush 3 SalesExecutive 4 Sandy Sandy Null Null Null Null null 10 Manager

 

How to debug a Stored Procedure in visual studio

There are two way to debug a stored procedure in visual studio.

I am going to discuss both one by one.
##############    1st method    ######################

open server explorer in visual studio.

open / add new connection to database.

drill down untill u find your store procedure.

right click on your procedure and then click Step Into Stored Procedure.

The Run stored procedure dialog box opens, which lists the parameters of the stored procedure if required. provide the input parameter if required and click OK
 

a window opens that displays the text of the stored procedure. 

The first executable line of the stored procedure is highlighted. Press F11 to step through the stored procedure to completion.
 

In the Output window, the following message is displayed, which indicates successful execution: 

 The program ‘SQL Debugger: T-SQL’ has exited with code 0 (0×0).

################    end 1st Method   ########################

################    2nd method    ###########################

create your application and write your desired code.

set a breakpoint in the code where you calls the store procedure.

like at

SqlDataReader dr = cmd.ExecuteReader();

line

In Solution Explorer, right-click the project (not the solution) and open the Property pages. Click Configuration Properties in the tree and then click to select the SQL Server Debugging check box on the Debugging page to enable stored procedure debugging.

In Server Explorer, locate and open the stored procedure . Right-click the stored procedure and then click Edit Stored Procedure.

set a Breakpoint in the store procedure where you want to debug.

run the application

Press F11. Code execution steps from the ExecuteReader method into the stored procedure window.

Press F11 again and procedure executes line by line as you press F11. Then control returns to your Visual Basic project, and the project runs to completion.

To continue to step through the Visual Basic code after you step out of the stored procedure, you must set a second breakpoint in the Visual Basic code after the call to the stored procedure. For example, you can set the second breakpoint on the following line:While (dr.Read)

##############    end 2nd Method   #####################

SQL Bulk copy in ADO.NET using C#

Sometimes you need to copy a large numbers of rows from any resources to sql server database. ASP.NET provides a class for that purpose in ADO.NET SqlBulkCopy.

Sometimes we need to copy a table data from one database to a table of another database.
To do so, ADO.NET provides SqlBulkCopy class


we can use this class to copy data from one resource to sql server.

I use following code to satisfy my need.

the namespace used for this

using System.Data.SqlClient;


code to copy bulk data


// code to establish connection to source datasource and fetching the data in DataTable.

        DataTable dtSource = new DataTable();
        string Sourceconstr = @"Data Source=IITCS1;Initial Catalog=Test;Integrated Security=SSPI;";
        SqlConnection Sourcecon = new SqlConnection(Sourceconstr);
        SqlDataAdapter daSource = new SqlDataAdapter("Select * from product", Sourcecon);
        daSource.Fill(dtSource);

 // Initializing an SqlBulkCopy object

string Destconstr="Data Source=IITCS2;Initial Catalog=ProductionTest;Integrated Security=SSPI";
SqlConnection Destcon = new SqlConnection(Destconstr);
SqlBulkCopy oSqlBulkCopy  = new SqlBulkCopy(Destcon);

// Copying data to destination

oSqlBulkCopy.DestinationTableName = "Test1";
oSqlBulkCopy.WriteToServer(dtSource);

// Closing connection and the others

oSqlBulkCopy.Close();

you can provide 
DataRow[] or
DataTable or
SqlDataReader


to the WriteToServer method of SQLBULKCOPY class as input parameter




Hope this code helps to find sort out your problem.

Enjoy coding..............................