Difference between Stored Procedure and Function in SQL

In many Interviews this question is very frequently asked by the interviewer, that what difference between Stored Procedures and Functions .So I am explaining some differences

1.         Stored Procedures in database are in compiled Format but Functions compiled at run time.

2.         We can use DML Statements (insert, update and delete database table) in stored procedure ,but in functions we cannot use DML Statements

3.         Function can be used with in stored procedures, but stored procedures cannot be used with in Functions.

4.         Functions can execute with select statements, but stored procedures cant not used with select statements, for executing procedure exec/execute key word we have to put before Procedure name.

5.         Functions can used as the column in select Statements, Stored procedure cannot.

6.         Functions can used with WHERE and HAVING ,Stored procedure Cannot be used with Where and having 

7.         In Functions only input parameter we can use, In stored procedure both input and output parameter can use.

8.         Functions must return a value, but in case of stored procedure they may or not return value.

9.         Functions can use with join because table valued function return table with can use in join ,stored procedure can not used with a  join

10.       In Function Table variable can be used temporary table cannot used with functions, In stored procedure table variable and temporary table both can used.

11.       In function exception handling cannot be done (we cannot use try and catch with in the functions),Exception handling  can be done with in stored procedure 

Examples

1.         In Functions only input parameter we can use, In stored procedure both input and output parameter can use.

                         Both input and output parameter can used with stored procedure.

                        We can not use output parameter in function. 

2.         DML  Statements in Stored procedures and Function

                            

                          we can use DML statements in stored procedure.

 

DML statement through error when using in function.

3.         Functions can used as the column in select Statements, Stored procedure cannot.

                           Function can use with select statement.

 

Stored procedure can not use with select statement.

4.         In function exception handling cannot be done (we cannot use try and catch with in the functions),Exception handling  can be done with in stored procedure 

                          In Stored Procedure we can use try catch.

 

In Function try catch not working.

Autor: Er. Yashodhara Sharma

Find Nth highest Salary without Using TOP and Max Keyword

For this we use Dense_Rank() Function

Now we will discuss how it works , and we will generate query for desired output step by step

There is a table Employee_Master which contain employee names and salary.

Now we will apply select query  and short it out in Descending order,This query will arrange the records in Descending order on the behalf of Salary.

select* from Employee_Master order by Salary desc

Now we are going to apply Dense_Rank() function, before applying this we need to understand that how it works ??

When we apply Dense_Rank() function it will create  one temporary  run time column which numbers the Salary according to the Rank priority like (1,2,3.....)

select Emp_Name,Salary,DENSE_RANK() over(order by Salary Desc)as RK from  Employee_Master

Now we can easily find the Nth highest salary from last query .

We will treat above last query as SUBQUERY and find out the Nth highest  salary with the help of Where Clause which will apply on Temporary Column(RK) like that-

If We want to find 3rd highest Salary-

select* from (select Emp_Name,Salary,DENSE_RANK() over(order by Salary Desc)as RK from  Employee_Master)as Employee_Master where RK=3

                                         
                                                      Author-Er. Rahul Kr. Yadav

Windows 10 is free for 6x more powerful Raspberry Pi 2

The successor to the world's favourite tiny computer, Raspberry Pi, has defied Moore's Law which predicts the amount of transistors on a processor chip will double every couple of years. In fact, the Pi v2 has six times the processing performance of its predecessor thanks to a new quad-core ARMv7 processor running at 900 MHz

How to get nth salary in sql

We are going to discuss very important Topic which usually asks in the Interviews.

We will create the required query step by step-

select * from Employee_Master

Firstly we need to specify what will be the output, for this we need to apply Desc order in select query, this query arrange all the records of  table in Descending Order on the Behalf of Salary.

select * from  Employee_Master order by Salary desc

Since We Need to find out Nth Highest salary(3rd),We will apply TOP keyword in last query.

select top(3) * from Employee_Master order by Salary desc



With the help of this output we can identify that what Should be the 3rd Highest salary .It will be 6000 which belongs to Employee Sagar, Because we have already selected Top 3 after applying Descending Order in select query.  

Now we need to  Generate query  for desired output.


We will use last query as Sub-Query and apply SELECT query on this subquery after that apply ASC order  and apply  TOP(1) on this query , it will generate desired output like that.

Select Top(1)* from (select top(3) * from Employee_Master order by Salary desc) as Employee_Master order by Salary asc

                                                   Author -Er. Rahul Kr. Yadav

Previewing Image in ASP.NET Image Control

If you want to upload an image through a file upload control in ASP.NET C# and want to preview of image with in upload time, write simple code to get out of here.

You can use given below function for it

<script src="//code.jquery.com/jquery-1.10.2.min.js" type="text/javascript"></script>

    <script type="text/javascript">

        function ShowpImage(input) {

            if (input.files && input.files[0]) {

                var reader = new FileReader();

                reader.onload = function (e) {

                    $('#ImgShow').attr('src', e.target.result);

                }

                reader.readAsDataURL(input.files[0]);

            }

        }

    </script>

 Now Full Code,

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!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> Show image preview on File upload Control</title>     <script src="//code.jquery.com/jquery-1.10.2.min.js" type="text/javascript"></script>     <script type="text/javascript">         function ShowpImage(input) {             if (input.files && input.files[0]) {                 var reader = new FileReader();                 reader.onload = function (e) {                     $('#ImgShow').attr('src', e.target.result);                 }                 reader.readAsDataURL(input.files[0]);             }         }     </script> </head> <body>     <form id="form1" runat="server">     <div>             <fieldset style="width: 250px;">             <legend>Show image preview before image upload</legend>             <div align="center">             <asp:Image ID="ImgShow" Height="150px" Width="150px" runat="server" /><br />             <asp:FileUpload ID="flupImage" runat="server" onchange="ShowpImage(this);" />             </div>         </fieldset>     </div>     </form> </body> </html>

Output 1

 

Now Click on Browse Button and select a image and click on open button.

Output 2

Microsoft wants to invest in Cyanogen to help take Android away from Google

                                                             

Microsoft is said to be investing in rival mobile operating system Cyanogen in an apparent attempt to help pry Android out of the hands of Google.