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
|
|||
37 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
Easy nd Best Tutorial i ever Read.....
hi how to get the data from sqlserver database and display in hmtl table using jquery in 3tier Architecture
nice one
i have an Error in this Code
that is
Procedure or Function 'AddUser' expects parameter '@ERROR', which was not supplied.
Thank you very much Sir!
Data is getting stored... But After storing the data I want user to go on to a new page.
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...
very good article
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
thank u very much suresh
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;
good one!!!!!!!!!!
sir, actually i m tring for how to upload multiple images and store in database using procedure
..............Nisha patil
sir whr exactly we supppose to write this code
Thanks alot Suresh.
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
sir can u plzz create video for this....??
sir very simple and true coding thanks
jai hind
Note: Only a member of this blog may post a comment.