Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Stored procedure with output parameter in sql server

Oct 19, 2010
Introduction:

Here I will explain how to write query to return output parameters in SQL Server.

Description:

In some situations we need to return the output value after execution of our query here I will explain with simple user registration here I am returning one out parameter if the query executes successfully else I am returning another output parameter .

Query:


CREATE PROCEDURE sp_userinformation
@UserName varchar(50),
@Password varchar(50),
@FirstName varchar(50),
@LastName varchar(50),
@Email varchar(50),
@PhoneNo varchar(50),
@Location varchar(50),
@Created_By varchar(50),
@ERROR VARCHAR(100) OUT

AS

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

---Checking Condition if User exists or not if user not exists returns different message if exists returns different message

IF NOT EXISTS(SELECT * FROM User_Information WHERE UserName=@UserName)
BEGIN
INSERT INTO User_Information
(
UserName,
[Password],
FirstName,
LastName,
Email,
PhoneNo,
Location,
Created_By
)
VALUES
(
@UserName,
@Password,
@FirstName,
@LastName,
@Email,
@PhoneNo,
@Location,
@Created_By
)
--If User Successfully Registerd I am returing this Message as Output Parameter
SET @ERROR=@UserName+' Registered Successfully'
END
ELSE
BEGIN
--If User already Exists i am returning this Message as Output Parameter
SET @ERROR=@UserName + ' Already Exists'
END
END

This way we will write queries to return output parameters

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

37 comments :

Asif Iqbal said...

Awesome trick to check user present.

It will helpful for me.

Thanks

Anonymous said...

good one mate

Anonymous said...

thanks suresh am learning so many thing bcos of you

Anonymous said...

thanks u so much suresh i am also learning so many thing bcos of you

Anonymous said...

thanks i already used in my Project

ProCoder_F said...

nice blog..

Unknown said...

code doesn’t work. plz reply. we didn’t get any error but the data is not stored in the database.

Suresh Dasari said...

@Roshni Nair,
Please check your code. i hope you did mistake in application.

Unknown said...

wen i was inserting values ,its not executing.
i have written like proc 'SHANKR',12000,10..error is Incorrect syntax near the keyword 'PROCEDURE'..
what should i do./?

Suresh Dasari said...

@shankar...
that is the problem with your procedure or the problem with passing parameters. please check it once...

abiramy said...

thank you suresh, your site makes my learning joyful, thankyou very much........

Anonymous said...

Thanks
It will help full for me.
How can add using system.data.sqlclient.sqlparameter
in project when it not show.

Rajendre said...

hi..
suresh thanks U i have one query about sql how to delete the row automatically after some days. and i want to add one snoozer like reminder after some time to reminde the popup in asp.net c#.plz guid me.

Unknown said...

sir,actually i am creating a sp which i declare a class library how i send a output parameter in asp.net

Unknown said...

sir,
this is my code for sp
============================
create procedure adminadd @name varchar(50),@psw varchar(20),@email varchar(50),@mob bigint,@sq varchar(500),@ans varchar(500),
@error varchar(500) out
as
begin
set nocount on
if not exists (select * from admin where aname=@name)
begin
insert admin values(@name,@psw,@email,@mob,@sq,@ans)
set @error=@name+'Thank U for Register Here'
end
else
begin
set @error =@name+'Already Exist'
end
end
=============
and i have declared a class in asp.net page
that are for calling insert admin register
===============
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

///
/// Summary description for Class1
///
public class Class1
{
public SqlConnection con;
public SqlDataAdapter adp;
public DataSet ds = new DataSet();
public SqlCommand cmd;
public SqlDataReader dr;

public Class1()
{

}

public void conn()
{
con = new SqlConnection(" server=.; database=shop; integrated security=true;");
con.Open();
}
public void insert(string pro, string val)
{
ds.Clear();
ds.Reset();
string sql = pro + "" + val;
adp = new SqlDataAdapter(sql, con);
adp.Fill(ds, pro);
}
==================
how to error message so in asp.net


==================

Unknown said...

hi sir my code is like dis
protected void btnsave_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("user id=sa;password=bharat;database=SALES;Data Source=2X12_HYD_DPC07\\SQLEXPRESS");
SqlCommand cmd = new SqlCommand("Sp_LeadInf0_IU ", con);
con.Open();
cmd.Parameters.AddWithValue("@Leadid", txtleadid.Text);
cmd.Parameters.AddWithValue("@Lead_Name", txtLeadname.Text);

cmd.Parameters.AddWithValue("@Company", txtcompany.Text);
cmd.Parameters.AddWithValue("@Address1", txtadd1.Text);
cmd.Parameters.AddWithValue("@Address2", txtadd2.Text); ;
cmd.Parameters.AddWithValue("@Address3", txtadd3.Text);
cmd.Parameters.AddWithValue("@Street", ddlstreet.Text);
cmd.Parameters.AddWithValue("@Area", ddlarea.Text);
cmd.Parameters.AddWithValue("@City", ddlcity.Text);
cmd.Parameters.AddWithValue("@contactperson", txtcontactperson.Text);
cmd.Parameters.AddWithValue("@desig", txtdesig.Text);
cmd.Parameters.AddWithValue("@mobile", txphn.Text);
cmd.Parameters.AddWithValue("@state", ddlstate.Text);
cmd.Parameters.AddWithValue("@country", ddlcountry.Text);
cmd.Parameters.AddWithValue("@emailid", txtemailid.Text);
cmd.Parameters.AddWithValue("@refferedby", txtrefferedby.Text);
cmd.Parameters.AddWithValue("@desc", txtdesc.Text);
cmd.Parameters.AddWithValue("@fax", txtfax.Text);
cmd.ExecuteNonQuery();
cmd.CommandType = CommandType.StoredProcedure;
[SALES]
GO
/****** Object: StoredProcedure [dbo].[Sp_LeadInf0_IU] Script Date: 04/04/2013 12:01:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select * from Lead_Information_Table
--select name from syscolumns
--where id=object_id('Lead_Information_Table')
ALTER PROCEDURE [dbo].[Sp_LeadInf0_IU]
@Lead_Id INT,
@Lead_Name NVARCHAR(125),
@Company NVARCHAR (60),
@Address1 NVARCHAR (50),
@Address2 NVARCHAR (50),
@Address3 NVARCHAR (50),
@Street NVARCHAR (40),
@Area NVARCHAR (50),
@City NVARCHAR(50),
@Contact_person NVARCHAR (20),
@Desig NVARCHAR (30),
@Mobile NVARCHAR (10),
@State NVARCHAR (50),
@Country NVARCHAR (50),
@Email_id NVARCHAR (60),
@Reffered_By NVARCHAR (50),
@Description NVARCHAR (max),
@fax NVARCHAR (80),
@Created_ModifiedBy NVARCHAR (80),
@Created_ModifiedOn DATETIME
AS
BEGIN
IF @LEAD_ID= ''
INSERT INTO Sp_LeadInf0_IU
(
Lead_Name,
Company,
Address1,
Address2,
Address3,
Street,
Area,
City,
Contact_person,
Desig,
Mobile,
State,
Country,
Email_id,
Reffered_By,
Description,
fax,
CreatedBy,
CreatedOn)
VALUES(
@Lead_Name,
@Company,
@Address1,
@Address2,
@Address3,
@Street,
@Area,
@City,
@Contact_person,
@Desig,
@Mobile,
@State,
@Country,
@Email_id,
@Reffered_By,
@Description,
@fax,
@Created_ModifiedBy,
@Created_ModifiedOn
)
ELSE
UPDATE dbo.Lead_Information_Table
SET

Lead_Name=@Lead_Name,
Company=@Company,
Address1=@Address1,
Address2=@Address2,
Address3=@Address3,
Street=@Street,
Area=@Area,
City=@City,
Contact_person=@Contact_person,
Desig=@Desig,
Mobile=@Mobile,
State=@State,
Country=@Country,
Email_id=@Email_id,
Reffered_By=@Reffered_By,
Description=@Description,
fax=@fax,
ModifiedBy=@Created_ModifiedBy,
ModifiedOn=@Created_ModifiedOn

END

my atore procedure is like dat im getting an error

Incorrect syntax near 'Sp_LeadInf0_IU'.

please help me



sankar said...

Nice one

Sumi said...

In your code, in stored procedure where it says Register successfully it is not displaying the message but getting saved in database.......Please Help

Anonymous said...

Easy nd Best Tutorial i ever Read.....

Unknown said...

hi how to get the data from sqlserver database and display in hmtl table using jquery in 3tier Architecture

Anonymous said...

nice one

Unknown said...

i have an Error in this Code
that is

Procedure or Function 'AddUser' expects parameter '@ERROR', which was not supplied.

Anonymous said...

Thank you very much Sir!

Anonymous said...

Data is getting stored... But After storing the data I want user to go on to a new page.

Unknown said...

pls Send me the Steps for Creating MVC From the begining how to start ant to end....eg file --> project-->MVC website....like that pls help me...

Anonymous said...
This comment has been removed by the author.
Unknown said...

very good article

Anonymous said...

SqlConnection con = new SqlConnection("Data Source=saurabh-PC;Initial Catalog=Mydb;Integrated Security=True");
which place i should use this conn string
and which place i use
connection with username and password

imran said...

thank u very much suresh

ravi said...

sir ,i m getting this error while running code

An SqlParameter with ParameterName '@ERROR ' is not contained by this SqlParameterCollection.

in code behind--------
cmd.Parameters.Add("@ERROR", SqlDbType.VarChar, 100);
cmd.Parameters["@ERROR "].Direction = ParameterDirection.Output;

Anonymous said...

good one!!!!!!!!!!

Anonymous said...

sir, actually i m tring for how to upload multiple images and store in database using procedure
..............Nisha patil

Anonymous said...

sir whr exactly we supppose to write this code

Unknown said...

Thanks alot Suresh.

Unknown said...

i have a problem
FileInfo Fi = new FileInfo(Fileupload1.PostedFile.FileName);
DateTime Date1 = Fi.LastWriteTime;
getting This time
"01/01/1601 00:00:00"
but this is wrong plz tell me how getting exact last modified,File Creation date or other info, datetime of uploaded file

Unknown said...

sir can u plzz create video for this....??

Kuldip Dagar said...

sir very simple and true coding thanks
jai hind

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.