473,699 Members | 2,773 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Creating SQL Stored Procedures

14 New Member
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
9 8976
vivekshrivastava51
18 New Member
hi ,
ya this is good method to writ stored procedure
Apr 20 '07 #2
jyothi1105
14 New Member
hi ,
ya this is good method to writ stored procedure
Thanks! For your comment
Apr 20 '07 #3
Frinavale
9,735 Recognized Expert Moderator Expert
Hi jyothi1105!

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

-Frinny
Apr 20 '07 #4
RedSon
5,000 Recognized Expert Expert
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
9,735 Recognized Expert Moderator Expert
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
vidz
2 New Member
Great!
Thank you for posting your codes and knowledge. This will help us..
Jan 15 '08 #7
prabur
3 New Member
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
9,735 Recognized Expert Moderator Expert
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
firozfasilan
42 New Member
Really very usefull resource
Mar 2 '08 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

6
6868
by: Dim St Thomas | last post by:
I am a developer working on a database client program. I am testing this program on a Windows XP machine (1.5 GHz AMD chip, 480 Mb RAM, 60 Gb disk) This machine has Oracle 9.2.0.1.0 and RedBrick database software installed. I am testing the software by creating small test databases. If I create an Oracle database using the Database Configuration Assistant, it takes forever just to create the database. If I choose to create a new database...
3
2783
by: Robin Tucker | last post by:
Hi there, I have a database on my test machine that will need to be installed on users machines. I would like to create the database with the given schema on the users machine and also with some suitable default values in the tables. I note that although I can script the schema so that re-creating the structure of the database is simple on the users machine, I cannot script the contents of the tables also (automatically). What I would...
2
9239
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered problems doing this. I wanted to implemented a generic "Helper" class like this: /** * Helper
0
2577
by: billmiami2 | last post by:
Perhaps many of you MS Access fanatics already know this, but it seems that stored procedures and views are possible in Jet. I thought I would leave this message just in case it would help anyone. I discovered this the other day while doing some experiments with ADO and ADO.NET. Basically, I wanted to run a stored MS Access query with parameters using the syntax Execute MyProcedure @Param1, @Param2...
5
3475
by: Tim Marshall | last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with interest and on reading the post describing Lauren Quantrell's SmartTree, I've run into something I don't understand: Stored Procedures. I thought stored pricedures were an Oracle/MS SQL Server thing and don't know how they work with Access Jet. I've looked at some of the help on stored procedures in A2003, but really don't understand what's going on. Can someone...
28
72496
by: mooreit | last post by:
The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and 2005 Test Environments. What is the purpose of a view if I can just copy the vode from a view and put it into a stored procedure? Should I be accessing views from stored procedures?
1
15855
by: Cuculain | last post by:
Anyone had problems with this? The Management Studio just saves my attempts to create stored procedures as query files and doesn't add them to the list of stored procedures.
2
6071
by: Andy | last post by:
Hi guys I having a problem creating a report in Access 2003 project talking to a SQL database through and ODBC connect. After hours of trying things from Access Help, MSDN and Google I still can't get it working. I have a query defined (view) and want the end user to put in a start date and end date to filter a report.
1
7763
by: Quish | last post by:
Hey I am creating a database application that is accessed through a .NET front end. What I want to do is run a SQL script that will create my DB, create my indexes and enforce my constraints (all of which I have done) but I also want to create my stored procedures in the same script also. When I merge all my stored procedures (about 16) into one file and run it in SQL Query Analyser I get multiple errors but the one that is coursing me...
0
8704
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8623
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9187
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9054
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8936
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7777
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6544
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5879
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4390
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.