Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to Write Store Procedure (SP) in SQL Server

Author: DevASP Team

In this article I will try to explain you how to write a store procedure in database (SQL Server 2005). This is a very basic article and it will help beginners in understanding the basic format of sto

Simple steps to write a store procedure in SQL Server 2005 that will help the novice/beginners in writing their first store procedure for inserting the data into database. Here the sample procedure which I will write will insert the binary file into database. This binary file passes to the procedure as parameter along with filetype and filename. This binary file parameter accepts the binary array which it reads from UI (User Interface). You can pass these parameters to store procedures using Parameters.Add method as argument. Here I will not go into the details of front end events that will use to save the values to database. To begin write away to the procedure you will first write the CREATE PROCEDURE command:

 

CREATE PROCEDURE Files_Insert

 

This simple command will create a procedure in database. Now add the parameter list to procedure. This parameter list contains the filetext, filename, filetype as I have discussed with you earlier:

 

      @p_FileID int output,

      @p_FileText varbinary(MAX) = null,

      @p_FileName varchar(50) = null,

      @p_FileType varchar(50) = null     

 

After adding parameter list to store procedure simply write the insert command. The @@IDENTITY will return the identity value of primary key value.

 

      INSERT INTO [Files]

           (

             [FileText],

             [FileName],

             [FileType]

            )

       VALUES

           (

             @p_FileText,

             @p_FileName,

             @p_FileType

            )

       SET @p_FileID = @@IDENTITY

 

Your final procedure will look like as follows:

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:       

-- Description:  

-- =============================================

CREATE PROCEDURE Files_Insert

      -- Add the parameters for the stored procedure here

      @p_FileID int output,

      @p_FileText varbinary(MAX) = null,

      @p_FileName varchar(50) = null,

      @p_FileType varchar(50) = null     

AS

BEGIN

      SET NOCOUNT ON;   

     

      INSERT INTO [Files]

           (

            [FileText],

            [FileName],

                  [FileType]

                  )

     VALUES

           (

                  @p_FileText,

                  @p_FileName,

                  @p_FileType

                  )

       SET @p_FileID = @@IDENTITY

     

 

END

GO

 

 

Article Comments
plz, sir insert quert sql server 2005 ,store proceder use,
plz insert query statement

Posted on 2/23/2008 1:05:01 AM by AMIT PATEL

Good and Easy to understandle

Posted on 2/26/2008 6:24:21 AM by A. Alagu Ganesh

you's websits is very useful and helpful

Posted on 4/29/2008 5:57:57 AM by kuldeep

Can you please tell me how to start and from where to start writing store procedures. and what will be the step before your this article.

Posted on 8/9/2008 6:59:00 AM by rahul

sir this site is very helpful and easy to understand

Posted on 8/18/2008 12:39:41 AM by salamath

Check this out that's cool

Posted on 8/23/2008 7:06:13 AM by Asad

how to use cursur for data inserting, please

Posted on 8/29/2008 7:55:58 AM by how to use cursur for data inserting... plzzz

Really its very useful.. Easy to understand... thank you

Posted on 9/24/2008 9:36:15 AM by Ramachandran

i hv to write sp using bussiness logic
i have one field aa,bb,cc,dd
i have to take each string seperated by comma and check if(aa='' )
if(bb='')
etc is it possible plz tell me

Posted on 11/28/2009 5:51:54 AM by Nazneen

Hi Nazneen,


you can use user defined split function which return you a table then you can apply your conditions on that

You can call funtion like that
select * from MySplit('aa,bb,cc,dd,ee,ff', ',')

Function definition is as follows

CREATE FUNCTION dbo.MySplit
(
@SplitString nvarchar(1000),
@SplitCharacter nvarchar(5)
)
RETURNS @RtnValue table
(
Data nvarchar(50)
)
AS
BEGIN
Declare @Count int
Set @Count = 1

While (Charindex(@ SplitCharacter,@ SplitString)>0)
Begin
Insert Into @RtnValue (Data)
Select
Data = ltrim(rtrim(Substring(@ SplitString,1,Charindex(@ SplitCharacter,@ SplitString)-1)))

Set @ SplitString = Substring(@ SplitString,Charindex(@ SplitCharacter,@ SplitString)+1,len(@ SplitString))
Set @Count = @Count + 1
End

Insert Into @RtnValue (Data)
Select Data = ltrim(rtrim(@ SplitString))

Return
END


Try this it will help you

Thanks

Posted on 11/30/2009 5:05:20 AM by zunnair

hie..please tell me how to store data using stored procedure through xml string in sql server 2005...me required this answer step by step.

Posted on 12/10/2009 4:30:22 AM by luvpreet

Plz tell me how to hash value input? (use MD5, SHA-1, 256,...).

Thanks!

Posted on 12/13/2009 10:30:22 PM by Thiên Long

Good Article But Tell Me How To call It in Code

Posted on 12/16/2009 1:39:09 AM by chinmay shah

Hi chinmay,

This is one way to use it...

Private objConn As New SqlConnection
Dim objCmd As New SqlCommand("DatabaseName.dbo.Proc_TEMP_Add")
objCmd.CommandType = CommandType.StoredProcedure
objCmd.Parameters.Add("@ID", SqlDbType.Int).Value = 1
objCmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = Me.txtName.Text

Dim DS As DataSet = SQLManager.GetDataSet(objCmd)


Thanks

Posted on 12/16/2009 3:47:22 AM by zunnair

best queries are their in to your web site

Posted on 12/23/2009 5:02:33 AM by anjanareddy

Private objConn As New SqlConnection
Dim objCmd As New SqlCommand("DatabaseName.dbo.Proc_TEMP_Add")
objCmd.CommandType = CommandType.StoredProcedure
objCmd.Parameters.Add("@ID", SqlDbType.Int).Value = 1
objCmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = Me.txtName.Text

Dim DS As DataSet = SQLManager.GetDataSet(objCmd)
Private objConn As New SqlConnection
Dim objCmd As New SqlCommand("DatabaseName.dbo.Proc_TEMP_Add")
objCmd.CommandType = CommandType.StoredProcedure
objCmd.Parameters.Add("@ID", SqlDbType.Int).Value = 1
objCmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = Me.txtName.Text

Dim DS As DataSet = SQLManager.GetDataSet(objCmd)


I want this code in C# in which place i have to made changes

Posted on 1/11/2010 6:33:55 AM by mandy

You have to change this all code in C# syntax.

Posted on 1/11/2010 10:06:37 AM by zunair

sir please tell me
where is stored procedure coding placed in sql server 2005

Posted on 1/29/2010 8:37:19 AM by desh deepak

this code given nicely but give step by step

Posted on 2/9/2010 1:16:53 AM by sandy

nice one n easy also

Posted on 2/26/2010 11:56:23 AM by vinay

there is code converter just copy paste it

Posted on 2/26/2010 2:08:31 PM by ashwin

Really good excercise.

Posted on 3/2/2010 1:09:29 PM by Pasah

How to execute a stored procedure in C#.net

Posted on 3/5/2010 1:12:44 AM by MOHANRAJ K

1) Create three databases namely DefaultDB, DB-A, DB-B.

2)Create a table (say tblInformation) in each of the databases with the columns [Person Name], [Age], [Sex], [Address].

3) Insert some values in all the four tables.

4) Create a Stored Procedure in DefaultDB (say usp_GetResult) like so:

CREATE PROCEDURE usp_GetResult
(
@FirstLetter varchar(50)
)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @MAINQRY AS VARCHAR(50)

SELECT @MAINQRY=
CASE @FirstLetter
WHEN 'A' THEN 'SELECT * FROM [DB-A].[dbo].[tblInformation]'
WHEN 'B' THEN 'SELECT * FROM [DB-B].[dbo].[tblInformation]'

ELSE 'SELECT * FROM [DefaultDB].[dbo].[tblInformation]'
END

EXEC(@MAINQRY)
END


How to Create this SP in MS SQl Server Managment Studio Express in ByDefault in .NET technology
Plz Replay me Immediate

Posted on 3/12/2010 2:50:01 AM by Bhushan

hai very thanks to gave this code it is very help full for me..

Posted on 3/25/2010 8:28:20 AM by balasubramanian

can any one plz tell me how to use cursor in a stored procedure

Posted on 4/3/2010 6:45:16 AM by javeed

It is very helpfull at initial level.....Thank you

Posted on 4/20/2010 12:12:50 AM by sanjay

First one is very good and helpfull for initial level...

Posted on 4/20/2010 12:14:14 AM by sanjay

plz sir how to write store processor in asp.net

Posted on 4/28/2010 4:20:01 AM by raju

Dear Sir/Mam
You haven't responded,, selected n paste my query regarding SQL stored procedure.
I really want to know all the types of store procedures,, their execution,, c# code,, an their uses in sql server 2008.
kindly sort out my prblms,, i am in training period of MCA from jaipur.Trainee in a reputated company through refrence..........

Posted on 4/28/2010 7:34:03 AM by shipra agarwal

its very usefull for me sir thank you

Posted on 4/30/2010 2:45:14 AM by RAVI ELLUR

add more basic and starting thing t

Posted on 4/30/2010 7:36:43 AM by aditya

plz help me i need step by step stored procedure creation process...based on calculation like (from-date) to (to-date)

Posted on 5/3/2010 4:28:38 AM by Nirmal Kumar

how to write storeprocedure for select statement

Posted on 5/7/2010 7:21:42 AM by manoj

How to Create this SP in MS SQl Server Managment Studio Express in ByDefault in .NET technology
Plz Replay me Immediately

Posted on 5/10/2010 2:28:51 AM by Manoj Kumar

your website is very useful for me , clearing every doughts in sql

Posted on 5/17/2010 4:00:05 PM by Ramanaiah

you'r website is very helpfull.I learned a lot from it.
thank you a lot.

Posted on 5/26/2010 11:13:01 AM by Nahid

can any one plz tell me what's the difference between user defined function and stored procedure.

Posted on 5/31/2010 2:13:19 AM by raka singh rathore

It was very helpful to me

Posted on 5/31/2010 7:52:59 AM by Perumalraj

the stored procedure can not be execute with the select command while function execute with the select command you can execute the store procedure with the execute statment

Posted on 5/31/2010 8:11:10 AM by Akila

Functions can be called from procedure whereas procedures cannot be called from function.

Posted on 5/31/2010 8:11:46 AM by Akila

Procedures can have input,output parameters for it whereas functions can have only input parameters.

Procedure can return zero or n values whereas function can return one value which is mandatory.

Posted on 5/31/2010 8:12:35 AM by Akila

this is very helpful everybody to learn sql server

Posted on 6/2/2010 6:27:29 AM by madhusudan

ok its fine.......

Posted on 6/2/2010 8:19:59 AM by ganesh

send for beginner how to create application to insert with the help of stored procedure

Posted on 6/4/2010 1:55:23 AM by nitin

very nice for begineres

Posted on 6/5/2010 2:31:24 AM by rajini

Awesome effort sir for making this site..............!

Posted on 6/7/2010 10:11:15 PM by Tahir Bangash

Good and Easy to understand

Posted on 6/19/2010 12:29:50 AM by kumar

thanks for ur kind support..

Posted on 6/28/2010 7:21:39 AM by pawan

Hi.............sir
This site is help full basic stored procedured.
I learned through this site sir.
.
have a nice day

Posted on 6/28/2010 2:23:25 PM by vijji

CREATE PROCEDURE example
-- Add the parameters for the stored procedure here

( @p_Name varchar(50) = null, @p_Course varchar(50) = null, @p_status varchar(50) = null)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
insert into example1table values('Ravi','BPT','Draft')
END
GO


-----the above written is my sql command to create stored procedure,
iam not able to execute the above procedure.please correct me

Posted on 6/29/2010 5:45:30 AM by Manasa

Very Good to Understand

Posted on 7/14/2010 5:17:00 AM by Manjoor alam

plz tell me the wt is the necessity of the storedprocedures.

Posted on 7/19/2010 1:24:33 AM by anupama

plz tell me the wt is the necessity of the storedprocedures.

Posted on 7/19/2010 2:04:44 AM by anupama

wt is the necessity of the storedprocedures

Posted on 7/19/2010 2:07:32 AM by anupama

Can you give me a breif discription about porcedure in SQL server

Posted on 7/23/2010 9:54:28 AM by vivek mishra

Can you give me a breif discription about porcedure in SQL server

Posted on 7/23/2010 9:55:05 AM by vivek mishra

Masha Allah Its Easy To Understand

Posted on 7/28/2010 8:08:03 AM by Asif Argami

How to create stored-procedure(SQL SERVER 2005) to insert data in two tables tbmMaster & tbmDetails. In tbmMaster i have to insert single values(from textbox,combobox) & in tbmDetails insert Multiple Values(from Datagridview) ) , actually i haven't used loop in storedprocedure . So in simple words how to create stored procedure to insert details of grid into database table in SQL SERVER 2005

Posted on 8/3/2010 1:49:56 AM by vishu

Cool understood a lot

Posted on 8/5/2010 4:36:01 AM by Ravindra

simple and easily understable

Posted on 8/14/2010 6:36:22 AM by Thila

Thanks, It's very simple to understand.
But, There is very different type of SP I ve seen.
That I can't understand.

Posted on 8/23/2010 7:01:36 AM by UMESH WANI

Hi , can we use "count" in sp. If no/yes then why

Posted on 8/27/2010 1:42:47 PM by Imti_yaz

thanks

Posted on 9/8/2010 5:03:55 AM by khadeer

This is insert query
create procedure sp_Emp
@Name Varchar(50),
@Phone varchar(10),
@Id int output
as
insert into emp_tbl(Name,Phone)Values(@Name,@Phone);
set @Id=@@Identity
end
__________________________________________________
This code to insert
_________________________________________________
SqlConnection conn=new Sqlconnection("Data Source=Localhost;Initial Catalog=Database name;Integrated Security=true);
sqlcommand comm;


conn.open();
comm=new Sqlcommand("sp_Emp",conn);
comm.commandType=commandType.StoredProcedure;
comm.parameters.add("@Name",SqlDbType.Varchar(50).value=txtName.text;
comm.parameters.add("@Phone",SqlDbType.Varchar(50).value=txtPhone.text;
comm.Parameters.add("@Id",SqlDbType.int);
comm.parameters["@Id"].Direction=parameterdirection.output;
comm.ExecuteNonQuery();
conn.close
___________________________________________________You try it chimmaye

Posted on 9/15/2010 7:48:55 AM by Kubendran

u could nt give compilation of sp

Posted on 9/28/2010 7:39:33 AM by narasimha


CREATE PROCEDURE SP_TEST

@ID NVARCHAR(20),
@NAME NVARCHAR(50)

AS
SET NOCOUNT ON

BEGIN TRAN

INSERT CUSTOMER (Customer_Name,Customer_Name)
VALUES(@ID,@NAME)

--Error Handling
IF @@ERROR <>0 SET GOTO PROBLEM

COMMIT TRAN
--Use this method to rollback
PROBLEM:
IF (@@ERROR<>0)
BEGIN
ROLLBACK TRAN


END

GO

Posted on 10/6/2010 6:55:30 AM by MINDADA

GOOD THINK

Posted on 10/11/2010 7:44:43 AM by AMK KHOKHAR

sir,can u plz tell me how to start Stored procedure and how to use,iam a beginner for stored procedure

Posted on 10/12/2010 9:01:28 AM by NagaSravan

Iam beginner in stored procedure..so can u plz give help me in coding

Posted on 10/12/2010 9:02:43 AM by NagaSravan

can u tell me how to count the no of parmeters in stored procedures

Posted on 11/18/2010 2:02:39 AM by karthikeyan

How can i get query result in a variable thru sql

:::select max(srno)-min(srno) from tablename:::

Posted on 12/1/2010 11:22:52 PM by Shivesh Narang

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< Tip to Handle Nulls in SQL SERVER 2005

Disclaimer - Privacy
© 2002-2017 DevASP.net