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:
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:
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:
[Code=sql]
CREATE PROCEDURE AddStudent @Sno VARCHAR(10),@Sn ame VARCHAR(20),@Sa ddress VARCHAR(50)
AS
Begin
INSERT INTO STUDENT(Sno ,Sname ,Saddress ) values (@Sname ,@Sname,@Saddre ss)
End[/Code]
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.
[Code=vbnet]
Private Sub SaveButton_Clic k() Handles SaveButton.Clic k
Dim conn As SqlConnection = New SqlConnection(" data Source=yourserv er/systemno;uid="u serID";pwd="pas sword";initial catalog=dbname" )
Try
conn.Open()
Dim strcmd As SqlCommand = New SqlCommand("stu ", conn)
strcmd.CommandT ype = CommandType.Sto redProcedure
strcmd.Paramete rs.Add("@sno", TextBox1.Text)
strcmd.Paramete rs.Add("@sname" , TextBox2.Text)
strcmd.Paramete rs.Add("@saddre ss", TextBox3.Text)
strcmd.ExecuteN onQuery()
conn.Close()
Catch ex As Exception
End Try
End Sub
End Class
[/code]
This code will take the values from asp page/form and then stored the values in backend SQLDB.
Hope this will help you.
Jyo!
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:
[Code=sql]
CREATE PROCEDURE AddStudent @Sno VARCHAR(10),@Sn ame VARCHAR(20),@Sa ddress VARCHAR(50)
AS
Begin
INSERT INTO STUDENT(Sno ,Sname ,Saddress ) values (@Sname ,@Sname,@Saddre ss)
End[/Code]
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.
[Code=vbnet]
Private Sub SaveButton_Clic k() Handles SaveButton.Clic k
Dim conn As SqlConnection = New SqlConnection(" data Source=yourserv er/systemno;uid="u serID";pwd="pas sword";initial catalog=dbname" )
Try
conn.Open()
Dim strcmd As SqlCommand = New SqlCommand("stu ", conn)
strcmd.CommandT ype = CommandType.Sto redProcedure
strcmd.Paramete rs.Add("@sno", TextBox1.Text)
strcmd.Paramete rs.Add("@sname" , TextBox2.Text)
strcmd.Paramete rs.Add("@saddre ss", TextBox3.Text)
strcmd.ExecuteN onQuery()
conn.Close()
Catch ex As Exception
End Try
End Sub
End Class
[/code]
This code will take the values from asp page/form and then stored the values in backend SQLDB.
Hope this will help you.
Jyo!
Comment