SQL Server Stored Procedures for Beginners
First, let’s create the header information (like the author, change log, etc.) that should be a part of every stored procedure.
/*
Name: usp_adduser
Description: Adds a user
Author: Tom O’Neill
Modification Log: Change
Description Date Changed By
Created procedure 7/15/2003 Tom O’Neill
*/
Remember this?
CREATE PROCEDURE usp_adduser
/*
We will put the variables in here, later
*/
Add the “CREATE PROCEDURE” line, assigning the name “usp_adduser”. Our next step is to remove the comments and declare our variables!
To start, let’s look at how our variables will fit. We will need to create a variable for every value we may need to pass. We may not pass a value to every field every time we run the stored procedure. But, we do need to address the possibility that over the life of the stored procedure, every data element may be used. The best way to address this issue is to create a variable for every column in USERLIST. To keep this example simple, we are also assuming that each of the columns can be NULL, and we will also be passing all of the variables to the stored procedure. If some of the columns cannot be NULL, or if not all of the columns will be affected, then the stored procedure and/or the exec statement have to be rewritten slightly. The list below shows the variable and the field with which it is associated.
-
@login—login
@pswd—pswd
@f_name—f_name
@l_name—l_name
@address_1—address_1
@address_2—address_2
@city—city
@state—state
@zipcode—zipcode
@email—email
You have probably noticed that I gave the variables names that closely resemble the column names with which they are associated. This will make it easier for you to maintain the stored procedure in the future. Delete the comments about variables, and put your list of variables beneath the “CREATE PROCEDURE” line.
CREATE PROCEDURE usp_adduser
@login
@pswd
@f_name
@l_name
@address_1
@address_2
@city
@state
@zipcode
@email
Next, add datatypes to each of the variables. The datatype assigned to the variable should match the datatype assigned to the corresponding column in the database. For any elements with the “char”, “varchar”, or “numeric” datatypes, you will need to put the maximum character length list in parentheses after the datatype. Separate all variables (except the last one), with a comma.
CREATE PROCEDURE usp_adduser
@login varchar(20),
@pswd varchar(20),
@f_name varchar(25),
@l_name varchar(35),
@address_1 varchar(30),
@address_2 varchar(30),
@city varchar(30),
@state char(2),
@zipcode char(10),
@email varchar(50)
creating and calling stored procedure in .net:
create procedure insert1(@userid int,@username varchar(50))
as
begin
insert into emp(id,name)values(@userid,@username)
end
NOTE:(id,name) are the columns of table in which you want to insert data.
CALLING STORED PROCEDURE :
initialize this globally:
public partial class1:class
{
sqlconnection con;
sqlcommand cmd;
}
on page_load(): // write on page load()
{
con=new sqlconnection(“Connection string”);
cmd=new sqlcommand();
cmd.connection=con;
cmd.commandtype=commandtype.stored procedure;
}
on button_click() // write on insert button_clik event
{
cmd.parameters.clear();
cmd.commandtext=”insert1″;
// stored procedure name just created.
cmd.parameters.addwithvalue(“@userid”,textbox1.text);
cmd.parameters.addwithvalue(“@username”,textbox2.text);
con.open();
cmd.executenonquery();
messagebox.show(“stored procedure inserted…”);
con.close();
}
Hi Tom,
I was reading your article and I would like to appreciate you for making it very simple and understandable. This article gives me a basic idea of stored procedure and it will help me a lot.
Thank you very much!
Bhuvan
Hi Tom
Your article are really awesome.actually i was in search for some good articles on stored procedures and finally i got one.
The most important is the simplicity which will be very helpful for the beginners.
Thanks
Avinash
Hi Tom,
This was a really informative article for us beginner SQL developers. The procedure was not very complex and easy to understand. I have been studying from a high level SQL developer in order to understand SQL better and he very much confused me with the very advanced code that he was using. Example of more complex code
/*
declare @today datetime
SET @Today = getdate()
EXECUTE sproc_insertvendor @name = ‘something Else’, @countofpayments = 3, @lastpaydate = @Today, @nextpaydate = @Today, @Comments = null
*/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE Name = ‘sproc_insertvendor’)
DROP PROCEDURE sproc_insertvendor
GO
CREATE PROCEDURE sproc_insertvendor
@name varchar(200),
@countOfPayments int,
@LastPayDate datetime,
@nextpaydate datetime,
@comments ntext
AS
if exists (
select *
from vendor v
where v.name = @name
)
BEGIN
Raiserror(‘%s already exists!’,16,2,@name)
END
if @@error=0
BEGIN
INSERT INTO vendor (Name,CountOfPayments,LastPayDate,NextPayDate,Comments)
VALUES (@Name,@CountOfPayments,@LastPayDate,@NextPayDate,@Comments)
SELECT @@Identity
END
RETURN
GO