Simple steps to create stored procedure in SQL Server : Using SQL Server Management Studio Or Visual Studio

Here is a simple tutorial on how to create and use stored procedure in SQL server with Visual Studio IDE or  SQL Server Management Studio. Stored procedures are the most secured, structured, efficient method to access data from SQL server.

Step 1 :

(If you have SQL Server Management Studio)

Open  SQL Server Management Studio and  choose the server to connect and provide username and password if necessary and

click on the connect button. If you are using SQL Server in your own computer then type . (dot)  in the place of server name . If you are using SQL Server Express Edition then type .\SQLEXPRESS in the place of server name. I am using SQL Server Express please check out the following screenshot.

(If you don’t have SQL Server Management Studio)

Open your visual studio IDE (Mine is visual studio 2010). Once you opened it open server explorer by clicking View->Server Explorer. Sometimes it may be opened by default. There will be two nodes by default  Data Connections and Servers.

Step 2 :

In this step we are going to create a database in SQL Server. Creating database with SQL Server Management Studio is quite easy if you don’t have it, it becomes a bit complicated. Let’s see how to create a database in SQL server with SQL Server Management Studio first.

Once you successfully connected to the database with SQL Server Management Studio, you can see a tree is at the right side of the window. Right click on the database folder and click New Database. You will get a window asking for the database name. Enter the desired database name and just click OK don’t bother about the other things like Logical Name . In my case I named the database to “car”.

Now, if you are in Database Explorer of Visual Studio, then right click on the Data Connections node and click on Create New SQL Server Database. Now you will be asked to enter the server name. If the SQL Server is running in the local system, enter dot (.) as server name and if you are using SQL Server Express Edition then enter .\SQLEXPRESS as server name. If the server name is correct, the text box for entering the database name will be enabled. Enter the desired database name and click OK.

 

Step 3:

In this step we have to create a table to store our data. This step onwards the process becomes same in both in SQL Server Management Studio and Server Explorer. Expand the databases folder in SQL Server Management Studio or expand newly created database name in Server Explorer. Right click on the Tables folder and click New Table. Add some fields to the new table and close it. You will be asked to save the table when you close the table design. In my case the following is my table design. And also you have to provide a name for the table that you just created. I named my table as “tblCar”

Step 4:

In this step we are going to create a stored procedure in SQL Server. Our first aim is to create a procedure to insert some values to the table. Open programmability (For SQL Server Management users)  folder and right click on the stored procedures folder and click New Stored Procedure Menu. Now you will get code editor with something in it. Clear all then add the following lines of code ( May change according to your table design).

CREATE PROCEDURE dbo.AddCar

       @CARID int,

       @CAR_NAME varchar(50),

       @VENDOR varchar(50)

AS

       insert into dbo.tblCar(ID,CAR_NAME,CAR_VENDOR)

       values(@CARID,@CAR_NAME,@VENDOR);

RETURN

And execute it to save it. Now you have one stored procedure under Stored Procedures folder. You can check the stored procedure that you created by executing it by right clicking it and clicking execute procedure. You may asked for input values if u provide parameters. In my case I have three parameters.

 

Now you learned how to create a stored procedure in SQL Server . The upcoming tutorials will help you to know how to use stored procedure with visual basic.