By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,685 Members | 1,031 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Creating SQL Stored Procedures

P: 14
Hi all,

Here is some information which could help people who want to create stored procedures and execute them in their program.

You can create stored procedures in two ways:
  • Through front end
  • or writing procedure from backend SQLServer.

Back End
First we'll cover how to write a stored procedure in back end.
The following example is how to create a store procedure for MSSql Server:
  • Open enterprise manager,
  • Expand the SQL Server Group,
  • Expand the Server,
  • Select "Databases" folder,
  • Expand the database the store procedure will be written for,
  • Right click "Stored Procedures",
  • Select "New Stored Procedure..."

This will open a dialogue box that will let you create a new Stored Procedure.
In this dialogue box you will enter the commands that will be executed when this store procedure is used.

The following is an example of a store procedure that will add an entry into the "Student" table:
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE AddStudent @Sno VARCHAR(10),@Sname VARCHAR(20),@Saddress VARCHAR(50) 
  2.  AS
  3. Begin
  4. INSERT INTO  STUDENT(Sno ,Sname ,Saddress ) values (@Sname ,@Sname,@Saddress)
  5. End
In this example, "AddStudent " is the procedure name and the parameters needed to execute procedure are: "@Sno", "@Sname", "@Saddress". "Sno","Sname","Saddress" are the column names for student table.

Using The Store Procedure In Code
After you create your store procedure in your database, you can use it in your code.

The following is an example of how to store student information into the database (using the store procedure created in the steps above) when the "Savebutton" is clicked.

Expand|Select|Wrap|Line Numbers
  1. Private Sub SaveButton_Click() Handles SaveButton.Click
  2.      Dim conn As SqlConnection = New SqlConnection("data Source=yourserver/systemno;uid="userID";pwd="password";initial catalog=dbname")
  3.      Try
  4.         conn.Open()
  5.         Dim strcmd As SqlCommand = New SqlCommand("stu", conn)
  6.         strcmd.CommandType = CommandType.StoredProcedure
  7.         strcmd.Parameters.Add("@sno", TextBox1.Text)
  8.         strcmd.Parameters.Add("@sname", TextBox2.Text)
  9.         strcmd.Parameters.Add("@saddress", TextBox3.Text)
  10.         strcmd.ExecuteNonQuery()
  11.         conn.Close()
  12.      Catch ex As Exception
  13.  
  14.      End Try
  15.     End Sub
  16. End Class
  17.  
This code will take the values from asp page/form and then stored the values in backend SQLDB.

Hope this will help you.
Jyo!
Apr 20 '07 #1
Share this Article
Share on Google+
9 Comments


P: 18
hi ,
ya this is good method to writ stored procedure
Apr 20 '07 #2

P: 14
hi ,
ya this is good method to writ stored procedure
Thanks! For your comment
Apr 20 '07 #3

Frinavale
Expert Mod 5K+
P: 9,731
Hi jyothi1105!

This is a great post.
Thank you so much for providing us with this resource!

-Frinny
Apr 20 '07 #4

RedSon
Expert 5K+
P: 5,000
Hi jyothi1105!

This is a great post.
Thank you so much for providing us with this resource!

-Frinny
Frin,

Do you want to format this original post a little better and put it in "Frinny's tip o' the week" thread and we can start a sticky thread for all your (and others') tips o' the week?
Apr 20 '07 #5

Frinavale
Expert Mod 5K+
P: 9,731
Frin,

Do you want to format this original post a little better and put it in "Frinny's tip o' the week" thread and we can start a sticky thread for all your (and others') tips o' the week?
This sounds like a great idea!
Apr 20 '07 #6

P: 2
Great!
Thank you for posting your codes and knowledge. This will help us..
Jan 15 '08 #7

P: 3
hi
like this stored procedure concepts...
hw can we store the datas into MS ACCESS files...
and hw can we retrive that.. using c#.....
Feb 26 '08 #8

Frinavale
Expert Mod 5K+
P: 9,731
hi
like this stored procedure concepts...
hw can we store the datas into MS ACCESS files...
and hw can we retrive that.. using c#.....
Hi Prabur,

You are going to have to use ADO.NET to connect to your MS Access database files using C#. After you have researched ADO.NET and attempted to solve your problem, please post any question or problems in the .NET forum to receive help.

Cheers!

-Frinny
Feb 26 '08 #9

P: 42
Really very usefull resource
Mar 2 '08 #10