Using Output keyword in Stored Procedure in SQL Server 2008

In this article I explain how to use output keyword in store procedure.
  • 1996

Introduction

In this article I explain how to use output keyword in store procedure.

First of all create a table

create table login(uIdd varchar(15),uPwdd varchar(15))

Insertion of data

insert into login
select 'admin','admin'union all
select 'user','user'

Output:

select * from login

 output-store-procedure-in-sql-server.jpg

Creation of store procedure:

create proc usp_select(@id varchar(15) output,@pwd varchar(15))
as
select * from login where uIdd=@id and uPwdd=@pwd

Output:

exec usp_select 'user','user'

 output-store-procedure-in-sql-server-.jpg

Now write the following source code in your web page.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="page1.aspx.cs" Inherits="page1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
        <tr><td>click here to get username</td><td><asp:Button ID="btn1" Text="get user Id" runat="server" OnClick="btn1_Click" /></td></tr>
        <tr><td>&nbsp;</td><td><asp:Label ID="lbl1" runat="server"></asp:Label></td></tr>
    </table>
    </div>
    </form>
</body>
</html>

Output:

output-store-procedure.jpg

Now write the following source code in your web page.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
 
public partial class page1 : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection("server=.;database=outputsp; user id=sa;pwd=wintellect");
    SqlCommand cmd;
 
    protected void Page_Load(object sender, EventArgs e)
    {
        con.Open();
        Response.Write("ya");
        con.Close();
 
    }
    protected void btn1_Click(object sender, EventArgs e)
    {
        string str="admin";
        cmd = new SqlCommand("usp_select", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@id", str);
        cmd.Parameters.AddWithValue("@pwd", str);
        con.Open();
        cmd.ExecuteNonQuery();
        cmd.Parameters.Add("@id", SqlDbType.Char, 500);
        cmd.Parameters["@id"].Direction = ParameterDirection.Output;
        string userName = (string)cmd.Parameters["@id"].Value;
        lbl1.Text = userName;
 
    }
}

Output:

 output-store-procedure-in-sql-server-2008.jpg

© 2013 dotNetheaven. All rights reserved.