Purvi's Blog: How to write stored procedure with output parameter in sql server

Thursday, 4 April 2013

How to write stored procedure with output parameter in sql server

How to write stored procedure with output parameter in sql server

In this article i will show you how you can create a new stored procedure in sql server . In this you will also learn how to create stored procedure with output parameter.

When we write stored procedure in that case in some situations we need some return some value depending upon operation.

so here is the stored procedure.

-- =============================================
-- Author:        Vinay Singh
-- Description:    In this we will check for duplicate student name
-- =============================================
CREATE PROCEDURE SP_Student
@Name varchar(50),
@Address varchar(50),
@Marks varchar(50),
@ContactNo varchar(50),
@ReturnMessage varchar(50) OUT
AS
BEGIN
SET NOCOUNT ON;
    IF NOT EXISTS(SELECT * FROM Student WHERE Name=@Name)
    Begin
        INSERT INTO [DemoArticles].[dbo].[Student]
           ([Name]
           ,[Address]
           ,[Marks]
           ,[ContactNo])
     VALUES
           (@Name,
           @Address,
           @Marks,
           @ContactNo)
           --This message will return if data get saved successfully.
           SET @ReturnMessage=@Name+`- Student data saved successfully`

    End
    Else
    Begin
        SET @ReturnMessage=@Name+` Student Name Already Exists`
    End
END
GO

This will return "successful message with student name " if data saved successfully, else it will return error message for duplicate name in data base.

How To Use RequiredFieldValidator control with DropDownList control For Validation

In this article i will show you how you can use requiredfieldvalidator control with dropdownList control for validation.

some of my previous articles are as follows.

How To Pass XML as Parameter to SQL Stored Procedure Using C# , jQuery UI Virtual Keyboard Plugin Example In Asp.Net | Virtual Keyboard Using jQuery , Jquery Code To Show Please Wait Message On Form Submit In Asp.net Using C# , Calling Insert Sql Server StoredProcedure Using Asp.Net In C# , How To Import Excelsheet Data Into SQL Server Using Asp.Net In c# , How To Pass Data Between Two Pages In Asp.net .

So for this article here is the code

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm7.aspx.cs" Inherits="WebApplication1.WebForm7" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">

    <div>
       <asp:DropDownList ID="DropDownList1" runat="server" Width="200px">
    </asp:DropDownList><asp:RequiredFieldValidator ID="RequiredFieldValidator1" ControlToValidate="DropDownList1" runat="server" ErrorMessage="Please select item."></asp:RequiredFieldValidator>
    </div>
    <br />
    <asp:button runat="server" text="Submit" onclick="Unnamed1_Click" />
    <br />
    <asp:Label ID="Label1" runat="server" style="color: #FF3300" Text=""></asp:Label>
    </form>
</body>
</html>

Now in your .cs page add the below code .

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication1
{
    public partial class WebForm7 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DropDownList1.Items.Add(new ListItem("Select Item", ""));
                DropDownList1.Items.Add(new ListItem("Item 1", "1"));
                DropDownList1.Items.Add(new ListItem("Item 2", "2"));
                DropDownList1.Items.Add(new ListItem("Item 3", "3"));
                DropDownList1.Items.Add(new ListItem("Item 4", "4"));
                DropDownList1.Items.Add(new ListItem("Item 5", "5"));
            }
        }

        protected void Unnamed1_Click(object sender, EventArgs e)
        {
            Label1.Text = "Page post take place.";
        }
    }
}

Now run the application for checking output.