Stored Procedures - Output Parameters & Return Values

Besides using input parameters, stored procedures can also return OUTPUT parameters and return values. Output parameters behave similarly to input parameters, but have to be declared with the OUTPUT keyword. In addition, you should specify the OUTPUT keyword when executing a stored procedure containing the output parameter to get the value. The following procedure contains an input parameter of the title type and an output parameter of total quantity of titles sold for the specified type:
1.
CREATE PROC sales_for_type @type VARCHAR(55), @total_sales INT OUTPUT
2.
AS
3.
SELECT SUM(qty) FROM sales a, titles b
4.
WHERE
5.
a.title_id = b.title_id
6.
and
7.
b.type = @type


This procedure can be executed as follows:
1.
DECLARE @total_sales_business int
2.
EXEC sales_for_type business, @total_sales=@total_sales_business OUTPUT


Results:
1.
-----------
2.
90


Notice that in order to use the output parameter, we have to declare a variable with the same data type as the output parameter of the called stored procedure. We can easily extend the same procedure to return more than one output parameters:
1.
ALTER PROC sales_for_type @type VARCHAR(55), @total_sales INT OUTPUT, @avg_sales INT OUTPUT
2.
AS
3.
SELECT SUM(qty), AVG(qty) FROM sales a, titles b
4.
WHERE
5.
a.title_id = b.title_id
6.
and
7.
b.type = @type


Now, we can execute the new procedure as follows:
1.
DECLARE @total_sales_business INT, @avg_sales_business INT
2.
EXEC sales_for_type business, @total_sales=@total_sales_business OUTPUT,
3.
@avg_sales = @avg_sales_business OUTPUT


Results:
1.
----------- -----------
2.
90 18


You can execute the stored procedure with an output parameter without the OUTPUT keyword, but you won't be able to use the returned value in the calling program.
Return values can be used within stored procedures to provide the stored procedure execution status to the calling program. The return values -99 through 0 are reserved for SQL Server internal use. You can create your own parameters that can be passed back to the calling program. By default, the successful execution of a stored procedure (or any group of SQL statements) will return 0. The syntax of the return command is:
1.
RETURN integer_value


You can check the result of executing a stored procedures with return values as follows:
1.
EXEC @return_variable = stored_procedure_name


where @return_variable is a numeric variable used to check the return value.
You can optionally enclose the integer value in parenthesis. If you don't supply the integer value, SQL Server will provide a value for you, depending on the state of program execution. RETURN also unconditionally exits the program, so once a RETURN is encountered in your T-SQL code SQL Server will not check any other conditions.
The following example demonstrates usage of user-defined return codes; notice that even though multiple conditions are examined within the procedure, a single RETURN will cause the program to stop and return the appropriate value:


01.
ALTER PROC sales_for_type @type VARCHAR(55), @total_sales INT OUTPUT, @avg_sales INT OUTPUT
02.
AS
03.
IF @type IS NULL
04.
BEGIN PRINT 'type is required' RETURN (1)
05.
END
06.
SELECT @total_sales=SUM(qty), @avg_sales = AVG(qty) FROM sales a, titles b
07.
WHERE
08.
a.title_id = b.title_id
09.
and
10.
b.type = @type
11.
IF @total_sales IS NULL
12.
AND @avg_sales IS NULL
13.
BEGIN
14.
RETURN (3) -- both avg and sum are null
15.
END
16.
IF @avg_sales IS NULL
17.
BEGIN
18.
RETURN (1) -- avg is null
19.
END
20.
IF @total_sales IS NULL
21.
BEGIN
22.
RETURN (2) -- total is null
23.
END


Now, we can execute the procedure with intentionally wrong values to check how the RETURN statement works:


1.
DECLARE @total_sales_business INT, @avg_sales_business INT, @return_status INT
2.
EXEC @return_status = sales_for_type tomato, @total_sales=@total_sales_business OUTPUT,
3.
@avg_sales = @avg_sales_business OUTPUT
4.
SELECT @return_status


Result:


1.
-----------
2.
3
3.
DECLARE @total_sales_business INT, @avg_sales_business INT, @return_status INT
4.
EXEC @return_status = sales_for_type NULL, @total_sales=@total_sales_business OUTPUT,
5.
@avg_sales = @avg_sales_business OUTPUT
6.
SELECT @return_status


Result:


1.
type is required
2.
-----------
3.
1

How to Create Wsdl from the Asmx or Svc files

Here is the code

wsdl /out:TempConversion.cs http://localhost/TempConversion/TempConversion.asmx

file path: C:\Program Files\Microsoft Visual Studio 9.0\VC

How to Call C# function using json

Here is the article about how to call asp.net function through javascript
<%@ Page Language="C#" AutoEventWireup="true" %>


<%@ Import Namespace="System.Collections.ObjectModel" %>
<%@ Import Namespace="System.Web.Services" %>


<script runat="server">
    [WebMethod]
    public static Collection<Location> FillDropDownList(int myValue1)
    {
        //use myValue1 to fill data
        var locations = new Collection<Location>
                                                 {
                                                     new Location {CountryID = 0, CountryName = "Please Select"},
                                                     new Location {CountryID = 1, CountryName = "Country1"},
                                                     new Location {CountryID = 2, CountryName = "Country2"},
                                                     new Location {CountryID = 3, CountryName = "Country3"},
                                                     new Location {CountryID = 4, CountryName = "Country4"},
                                                     new Location {CountryID = 5, CountryName = "Country5"}
                                                 };
        return locations;
    }
    public class Location
    {
        public int CountryID { get; set; }
        public string CountryName { get; set; }
    }
    
</script>


<!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>


    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"></script>


    <script type="text/javascript">
        $(function() {


            $('#<%= FillDropDownListButton.ClientID %>').click(function() {
                doAjaxCall('Default.aspx/FillDropDownList');
                return false;
            });
            function doAjaxCall(url, data) {
                var param1 = 1;
                $.ajax({
                    type: 'POST',
                    url: url,
                    data: '{myValue1: ' + param1 + '}',
                    contentType: 'application/json; charset=utf-8',
                    dataType: 'json',
                    success: successHandler
                });
            }


            function successHandler(response) {
                var myDropDownList = $('#<%= MyDropDownList.ClientID %>');
                myDropDownList.find('options').remove();
                var data = response.d;
                var doc = $('<div></div>');
                for (var i = 0; i < data.length; i++) {
                    doc.append($('<option></option>').
                            attr('value', data[i].CountryID).text(data[i].CountryName)
                            );
                }
                myDropDownList.append(doc.html());
                doc.remove();
            }


        });
    </script>


</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DropDownList ID="MyDropDownList" runat="server">
        </asp:DropDownList>
        <asp:Button ID="FillDropDownListButton" runat="server" Text="Fill DropDownList" />
    </div>
    </form>
</body>
</html>

How To Find Control in gridview on RowCommand event in asp.net

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)


    {


        if (e.Row.RowType == DataControlRowType.DataRow)


        {


            Label lblPrice = (Label)e.Row.FindControl("lblPrice");


            Label lblUnitsInStock = (Label)e.Row.FindControl("lblUnitsInStock");






            decimal price = Decimal.Parse(lblPrice.Text);


            decimal stock = Decimal.Parse(lblUnitsInStock.Text);






            totalPrice += price;


            totalStock += stock;






            totalItems += 1;


        }






        if (e.Row.RowType == DataControlRowType.Footer)


        {


            Label lblTotalPrice = (Label)e.Row.FindControl("lblTotalPrice");


            Label lblTotalUnitsInStock = (Label)e.Row.FindControl("lblTotalUnitsInStock");






            lblTotalPrice.Text = totalPrice.ToString();


            lblTotalUnitsInStock.Text = totalStock.ToString();






            lblAveragePrice.Text = (totalPrice / totalItems).ToString("F");


        }


    }

How to find control on GridView RowCommand Event?

Today We Will Learn 
Q1.How to find a control within a gridview to get some data of that control.
<Columns>
 <asp:TemplateField HeaderText="Team">
    <ItemTemplate>
     <asp:Label ID="lblTeam" runat="server" Text='<%# Eval("TeamName")%>'></asp:Label>
   </ItemTemplate>
 </asp:TemplateField>
</Columns> 
<Columns>
 <asp:TemplateField>
    <ItemTemplate>
       <asp:LinkButton ID="lnkEdit" Text="EDIT" runat="server" CssClass="coachEdit"   CommandArgument='<%# Eval("TeamId")%>' CommandName="Redirect"></asp:LinkButton>
   </ItemTemplate>
 </asp:TemplateField>
</Columns>




On the Row_command Use this Code to get the value of the control


GridViewRow row = (GridViewRow)(((LinkButton)e.CommandSource).NamingContainer);
 Label lblDate = (Label)row.Cells[0].FindControl("lblDate");
string teamName= lblDate.Text; 
int rowindex = row.RowIndex;