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 Email
|
|||
|
|
18 comments :
Awesome trick to check user present.
It will helpful for me.
Thanks
good one mate
thanks suresh am learning so many thing bcos of you
thanks u so much suresh i am also learning so many thing bcos of you
thanks i already used in my Project
nice blog..
code doesn’t work. plz reply. we didn’t get any error but the data is not stored in the database.
@Roshni Nair,
Please check your code. i hope you did mistake in application.
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./?
@shankar...
that is the problem with your procedure or the problem with passing parameters. please check it once...
thank you suresh, your site makes my learning joyful, thankyou very much........
Thanks
It will help full for me.
How can add using system.data.sqlclient.sqlparameter
in project when it not show.
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.
sir,actually i am creating a sp which i declare a class library how i send a output parameter in asp.net
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
==================
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
Nice one
In your code, in stored procedure where it says Register successfully it is not displaying the message but getting saved in database.......Please Help