Difference Between Stored Procedure andUser Defined Function in SQL Server

Stored Procedure

A Stored Procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again. So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a Stored Procedure and then just call the Stored Procedure to execute the SQL code that you saved as part of the Stored Procedure.

In addition to running the same SQL code over and over again you also have the ability to pass parameters to the Stored Procedure, so depending on what the need is, the Stored Procedure can act accordingly based on the parameter values that were passed.

Stored Procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determine which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a Stored Procedure, they become part of a single execution plan on the server. The results do not need to be returned to the client to have the conditional logic applied; all of the work is done on the server.

Benefits of Stored Procedures

Precompiled execution: SQL Server compiles each Stored Procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when Stored Procedures are called repeatedly.

Reduced client/server traffic: If network bandwidth is a concern in your environment then you'll be happy to learn that Stored Procedures can reduce long SQL queries to a single line that is transmitted over the wire.

Efficient reuse of code and programming abstraction: Stored Procedures can be used by multiple users and client programs. If you utilize them in a planned manner then you'll find the development cycle requires less time.

Enhanced security controls: You can grant users permission to execute a Stored Procedure independently of underlying table permissions.
User Defined Functions

Like functions in programming languages, SQL Server User Defined Functions are routines that accept parameters, perform an action such as a complex calculation, and returns the result of that action as a value. The return value can either be a single scalar value or a result set.

Functions in programming languages are subroutines used to encapsulate frequently performed logic. Any code that must perform the logic incorporated in a function can call the function rather than having to repeat all of the function logic.

SQL Server supports two types of functions

Built-in functions: Operate as defined in the Transact-SQL Reference and cannot be modified. The functions can be referenced only in Transact-SQL statements using the syntax defined in the Transact-SQL Reference.

User Defined Functions: Allow you to define your own Transact-SQL functions using the CREATE FUNCTION statement. User Defined Functions use zero or more input parameters, and return a single value. Some User Defined Functions return a single, scalar data value, such as an int, char, or decimal value.
Benefits of User Defined Functions

They allow modular programming: You can create the function once, store it in the database, and call it any number of times in your program. User Defined Functions can be modified independently of the program source code.

They allow faster execution: Similar to Stored Procedures, Transact-SQL User Defined Functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times. CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic.

Transact-SQL functions are better suited for data-access intensive logic.
They can reduce network traffic: An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. The function can then invoked in the WHERE clause to reduce the number or rows sent to the client.

Differences between Stored Procedure and User Defined Function in SQL Server

Sr.No.User Defined FunctionStored Procedure
1 Function must return a value.Stored Procedure may or not return values.

2Will allow only Select statements, it will not allow us to use DML statements.Can have select statements as well as DML statements such as insert, update, delete and so on

3 It will allow only input parameters, doesn't support output parameters.It can have both input and output parameters.
4It will not allow us to use try-catch blocks.For exception handling we can use try catch blocks.

5Transactions are not allowed within functions.Can use transactions within Stored Procedures.

6We can use only table variables, it will not allow using temporary tables.Can use both table variables as well as temporary table in it.

7Stored Procedures can't be called from a function.Stored Procedures can call functions.

8 Functions can be called from a select statement.Procedures can't be called from Select/Where/Having and so on statements. Execute/Exec statement can be used to call/execute Stored Procedure.

9A UDF can be used in join clause as a result set.Procedures can't be used in Join clause

Pivot table in sql

Simple Way To Use Pivot In SQL Query
Maksud Saifullah Pulak, 4 Dec 2015 CPOL

    4.70 (75 votes)
Rate this:
vote 1vote 2vote 3vote 4vote 5
Transforming data from row-level data to columnar data.
Introduction
This is a very simple example of Pivot query for the beginners. We use pivot queries when we need to transform data from row-level to columnar data.

Pivot query help us to generate an interactive table that quickly combines and compares large amounts of data. We can rotate its rows and columns to see different summaries of the source data, and we can display the details for areas of interest at a glance. It also help us to generate Multidimensional reporting.

Background
This post intends to help T-SQL developers get started with PIVOT queries. Most business applications will need some sort of PIVOT queries and I am sure many of you must have come across pivoting requirements several times in the past.

Using the Code
Let us have a table name Invoice which has three properties, InvoiceNumber, InvoiceDate, InvoiceAmount. Suppose we have several rows input in the table. Our goal is to display the sum of InvoiceAmount each month.

Hide   Copy Code
SELECT * FROM (SELECT year(invoiceDate) as [year], left(datename(month,invoicedate),3)as [month], _
InvoiceAmount as Amount FROM Invoice) as InvoiceResult

Hide   Copy Code
SELECT *
FROM (
    SELECT
        year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month],
        InvoiceAmount as Amount
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr,
    may, jun, jul, aug, sep, oct, nov, dec)
)AS pvt

License
This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ADO.NET DataView in ASP.NET

ADO.NET DataView in ASP.NET
By Rohatash Kumar on Feb 01, 2012
A DataView provides various views of the data stored in a DataTable. Using a DataView, you can expose the data in a table with different sort orders, and you can filter the data by row state or based on a filter expression.

A DataView provides various views of the data stored in a DataTable. Using a DataView, you can expose the data in a table with different sort orders, and you can filter the data by row state or based on a filter expression. That is we can customize the views of data from a DataTable.

Creating Table in SQL Server Database

Now create a table named UserDetail with the columns UserID and UserName. The table looks like as below.

Now insert some data into the table.

Now creating a new web application project in Visual Studio 2010. Now add the following namespace.

using System.Data.SqlClient;
using System.Data;

Now write the connection string to connect to the database.

string strConnection = "Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;";

Here in aspx code, I used a DataGrid.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication120.WebForm1" %>
<!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:GridView ID="GridView1" runat="server">
        </asp:GridView>  
    </div>
    </form>
</body>
</html>

Now we create a simple application showing the SQL Server Table Data in the GridView. The following code is the simple code without using DataView.

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

namespace WebApplication120
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            show();
        }
        private void show()
        {
            {
                SqlConnection con = new SqlConnection("Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;");
                string strSQL = "Select * from UserDetail";
                SqlDataAdapter dt = new SqlDataAdapter(strSQL, con);
                DataSet ds = new DataSet();
                dt.Fill(ds, "UserDetail");
                con.Close();
                GridView1.DataSource = ds;
                GridView1.DataBind();
            }
        }
    }
}

Creating a DataView

To convert a DataSet to a DataView in ASP.Net using C# code, you can initialize the DataView class object by accessing the DefaultView property via DataTable collection of DataSet. DefaultView property enables you to convert the DataSet to DataView.

SqlConnection con = new SqlConnection("Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;");
                string strSQL = "Select * from UserDetail";
                SqlDataAdapter dt = new SqlDataAdapter(strSQL, con);
                DataSet ds = new DataSet();
                dt.Fill(ds, "UserDetail");
                con.Close();
                DataView dv = new DataView();              
                GridView1.DataSource = ds.Tables[0].DefaultView;
                GridView1.DataBind();

Now run the application.

Adding new row in the DataView

We can add new rows in the DataView using AddNew() method in the DataView. The following C# source code shows how to add a new row in a DataView.

SqlConnection con = new SqlConnection("Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;");
                string strSQL = "Select * from UserDetail";
                SqlDataAdapter dt = new SqlDataAdapter(strSQL, con);
           

Email and Mobile No Validations in asp.net

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeBehind="Default.aspx.cs" Inherits="Test_Website_13_12_2015._Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
<script  type="text/javascript" >
    function validateEmail_Ext() {

   
    }

    function validateTxt() {
        var obj = document.getElementById('<%= txtEmail.ClientID %>');
        var regex = /^[a-zA-Z0-9._-]+@([a-zA-Z0-9.-]+\.)+[a-zA-Z0-9.-]{2,4}$/;
        if (regex.test(obj.value)) {
            //You can also assign stylesheet by
            //obj.className='....';
            obj.style.backgroundColor = '';
            obj.style.backgroundColor = '';
            alert("valid Email Address");
        }
        else {
            //Changing Background Color, so that user can understand that its invalid
            //You can also assign stylesheet by
            //obj.className='....';
            alert("please enter valid email");
            obj.style.backgroundColor = '#FD5E53';
            obj.style.borderColor = '#CD4A4A';
            return false;
        }

        var inputtxt = document.getElementById('<%= TxtPhone.ClientID  %>');
        //     var phoneno = /^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{8})$/;
               var phoneno = /^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{8})$/;
  if(inputtxt.value.match(phoneno)) {
      alert("valid Phone Number");
         
     }
   else {
       inputtxt.style.backgroundColor = '#FD5E53';
       inputtxt.style.borderColor = '#CD4A4A';
         alert("Not a valid Phone Number");
       return false;
     }

   return true;

    }
</script>
</asp:Content>

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">

    <h2>
        Welcome to ASP.NET!
    </h2>
    <p>
        To learn more about ASP.NET visit <a href="http://www.asp.net" title="ASP.NET Website">www.asp.net</a>.
    </p>
    <p>
        You can also find <a href="http://go.microsoft.com/fwlink/?LinkID=152368&amp;clcid=0x409"
            title="MSDN ASP.NET Docs">documentation on ASP.NET at MSDN</a>.
    </p>

   <asp:TextBox ID='txtEmail' runat="server" placeholder="demo@demo.com"></asp:TextBox>
   <br />
   <asp:TextBox ID='TxtPhone' runat="server" placeholder="022-252-12345678"></asp:TextBox>
   <br />
<%--<asp:Button id="cmdTest" runat="server" Text="Check Email Address" OnClientClick="validateEmail();" /><br />--%>
<asp:Button id="cmdTest_Ext" runat="server" Text="Save" OnClientClick="return validateTxt();" />

<p>

</p>
</asp:Content>

Software Development Life Cycle ( SDLC )

Software Development Life Cycle, SDLC for short, is a well-defined, structured sequence of stages in software engineering to develop the intended software product.

SDLC Activities:

SDLC provides a series of steps to be followed to design and develop a software product efficiently. SDLC framework includes the following steps:

Communication:

This is the first step where the user initiates the request for a desired software product. He contacts the serviceprovider and tries to negotiate the terms. He submits his request to the service providing organization in writing.

Requirement Gathering:

This step onwards the software development team works to carry on the project. The team holds discussions with various stakeholders from problem domain and tries to bring out as much information as possible on their requirements. The requirements are contemplated and segregated into user requirements, system requirements and functional requirements. The requirements are collected using a number of practices as given -studying the existing or obsolete system and software,conducting interviews of users and developers,referring to the database orcollecting answers from the questionnaires.

want to Find Control in gridview on RowCommand event in asp.net

Dear All

I want to Find Control in gridview on RowCommand event in asp.net
if it Is possible Please revert me

Reply | Reply with Attachment
Alert Moderator
Responses

Posted by: Prabhakar on: 5/10/2011 [Member] [MVP] Starter | Points: 25

0

Hi avalemanoj0405

i am Provide Code For Find Control ... Check it now. . . it's a use full for you . . on RowDataBound

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");

        }

    }

Best Regard's
Prabhakar

Navalemanoj0405, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Navalemanoj0405 on: 5/10/2011 [Member] Starter | Points: 25

0

thanks for reply prabhakar
but i want to find control on RowCommand of gridview.

Navalemanoj0405, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Prabhakar on: 5/10/2011 [Member] [MVP] Starter | Points: 25

0

ok Navalemanoj0405

check this code. . if u agree mark as Answer . .

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

    {

        if (e.CommandName.Equals("Update"))

        {

            int state = 0;

            int index = int.Parse(e.CommandArgument.ToString());

            GridViewRow row = GridView1.Rows[index];

            DropDownList lstState = (DropDownList)row.FindControl("StateID");

            state = int.Parse(lstState.SelectedValue.ToString());

            ObjectDataSource1.UpdateParameters.Add("StateID", state.ToString());

        }     

    }

Best Regard's
Prabhakar

Navalemanoj0405, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Navalemanoj0405 on: 5/10/2011 [Member] Starter | Points: 25

0

hey prabhakar but what should be there in CommandArgument in gridview ?

Navalemanoj0405, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Prabhakar on: 5/10/2011 [Member] [MVP] Starter | Points: 25

0

Hi

here the Command-argument To determine the index of the row that raised the event, use the CommandArgument property of the event argument that is passed to the event.

Best Regard's
Prabhakar

Navalemanoj0405, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Navalemanoj0405 on: 5/10/2011 [Member] Starter | Points: 25

0

Hi
CommandArgument giving me empty string
What i should do?

Navalemanoj0405, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Prabhakar on: 5/10/2011 [Member] [MVP] Starter | Points: 25

0

Hi..

if u click on Add or update button so CommandArgument given not empty . . Show ur code else . . Check this link . .

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.rowcommand.aspx#Y2793

Best Regard's
Prabhakar

Navalemanoj0405, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ndebata on: 5/10/2011 [Member] Starter | Points: 25

Getting Textbox value in Javascript

I am trying to get the value of the textbox in a javascript, but its not working. Given below is the code of my test page

<%@ Page Title="" Language="VB" MasterPageFile="~/Site.Master" AutoEventWireup="false"
CodeFile="test3.aspx.vb" Inherits="test3" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="Server">
<script language="javascript">

    function GetAlert() {

        var TestVar = document.getElementById('txt_model_code').value;
        alert(TestVar);

    }

</script>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="Server">
<asp:TextBox ID="txt_model_code" runat="server" ></asp:TextBox><br /><br />

<input type="button" value="db Function" onclick="GetAlert()" /><br />
</asp:Content>
So where am i going wrong?? How to get the text entered in the textbox in the javascript???

share improve this question
Asked
Feb 18 '11 at 9:22

Shijilal
819●6●20●39
6  
do this: var TestVar = document.getElementById('<%= txt_model_code.ClientID %>').value; – deostroll Feb 18 '11 at 9:25
   
@deostroll thanks it worked.. Now what to do if i want to show that value in another textbox – Shijilal Feb 18 '11 at 9:42
2  
document.getElementById('<%= anothertextboxid.ClientID %>').value=TestVar – Nirmal Feb 18 '11 at 9:48
   
as it is asp.net control, you can not get the value using getElementById(). because asp.net controls are converted to html controls and "id" changes on the browser. for more details refer: coding-issues.blogspot.in/2013/10/… – Ranadheer Reddy Oct 31 '13 at 9:36
add a comment
6 Answers order by 
up vote
11
down vote
accepted
Use

document.getElementById('<%= txt_model_code.ClientID %>')
instead of

document.getElementById('txt_model_code')`
Also you can use onClientClick instead of onClick.

share improve this answer
Answered
Feb 18 '11 at 9:38

Nirmal
965●7●25 Edited
Jun 7 '13 at 15:20

David L
13.5k●3●18●43
   
when i use the function in text_model_code onTextChange, it's saying the function is not part of my aspx file.. Why??? <asp:TextBox ID="txt_model_code" runat="server" OnTextChanged="GetModelPageMethod()"> – Shijilal Feb 18 '11 at 10:03
1  
I think you need to use this in onChange="GetModelPageMethod();" – Nirmal Feb 18 '11 at 10:07
   
ohk thanks..it worked – Shijilal Feb 18 '11 at 10:15
add a comment
up vote
4
down vote
This is because ASP.NET it changing the Id of your textbox, if you run your page, and do a view source, you will see the text box id is something like

ctl00_ContentColumn_txt_model_code

There are a few ways round this:

Use the actual control name:

var TestVar = document.getElementById('ctl00_ContentColumn_txt_model_code').value;

use the ClientID property within ASP script tags

document.getElementById('<%= txt_model_code.ClientID %>').value;

Or if you are running .NET 4 you can use the new ClientIdMode property, see this link for more details.

http://weblogs.asp.net/scottgu/archive/2010/03/30/cleaner-html-markup-with-asp-net-4-web-forms-client-ids-vs-2010-and-net-4-0-series.aspx1