SQL Server Stored Procedures for Beginners
Database Script to Create Tables for Exercises
ALTER TABLE [dbo].[USERDETAILS] DROP CONSTRAINT FK_USERDETAILS_USERLIST
GO
if exists (select * from sysobjects where id = object_id(N’[dbo].[USERDETAILS]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[USERDETAILS]
GO
if exists (select * from sysobjects where id = object_id(N’[dbo].[USERLIST]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[USERLIST]
GO
CREATE TABLE [dbo].[USERDETAILS] (
[detail_id] [int] IDENTITY (1, 1) NOT NULL ,
[usr_id] [int] NOT NULL ,
[title] [varchar] (50) NULL ,
[yrs_service] [numeric](18, 0) NULL ,
[yrs_title] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[USERLIST] (
[usr_id] [int] IDENTITY (1, 1) NOT NULL ,
[login] [varchar] (20) NOT NULL ,
[pswd] [varchar] (20) NOT NULL ,
[f_name] [varchar] (25) NULL ,
[l_name] [varchar] (35) NOT NULL ,
[address_1] [varchar] (30) NULL ,
[address_2] [varchar] (30) NULL ,
[city] [varchar] (30) NULL ,
[state] [char] (2) NULL ,
[zipcode] [char] (10) NULL , [email] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
About the Author
Tom O’Neill is a Senior Consultant in the Solutions/IDI practice at Deloitte & Touche. Tom’s areas of expertise center web application development, database architecture, and marketing automation. Tom O’Neill can be reached at 617-437-2945 or at tomoneill@deloitte.com.
Solutions/IDI, of Deloitte & Touche, is a technology consulting practice that provides various technology and IT-related services worldwide.
Published with the express written permission of the author. Copyright
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