Difference Between Stored Procedure and Functions

Stored procedure
A stored procedure is a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like PL/SQL for Oracle database or PL/PgSQL for PostgreSQL. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

User-defined function
A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

In SQL Server 2000
User defined functions have 3 main categories

1. Scalar-valued function - returns a scalar value such as an integer or a timestamp. Can be used as column name in queries
2. Inline function - can contain a single SELECT statement.
3. Table-valued function - can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows, but you can't enclose the logic for getting this rowset in a single SELECT statement. 

Differences between Stored procedure and User defined functions

1. UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
2. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
3. Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
4. Of course there will be Syntax differences and here is a sample of that

Stored procedure

Code: SQL

CREATE PROCEDURE dbo.StoredProcedure1
  /*
     (
      @parameter1 datatype = default value,
      @parameter2 datatype OUTPUT
     )
  */
  AS
     /* SET NOCOUNT ON */
     RETURN

User defined functions

Code: SQL

CREATE FUNCTION dbo.Function1
     (
     /*
     @parameter1 datatype = default value,
     @parameter2 datatype
     */
     )
  RETURNS /* datatype */
  AS
     BEGIN
      /* sql statement ... */
     RETURN /* value */
     END

Some More Differences


Although both functions and sp's are prcomiled sql statements there exists some differences between them.

1. Functions must return a value(scalar,inline table or multi statement table) whereas stored proc may or may not retun a value.
2.Functions can return a table whereas stored procs can create a table but can't return table.
3. Stored procs can be called independently using exec keyword whereas function are called using select statements.
4. Stored procs can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function can't be used for this
5. XML and output parameters can't be passed to functions whereas it can be with sp's.
6.transaction related statement can be handled in sp whereas it can't be in function.
7. stored procedures can call a funtion or another sstored proc similarly a function can call another function and a stored proc.The catch with function is that no user defined stored proc can be called.Only extended/system defined procs can be called.



OR

Functions
----------
1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You can join UDF
4) Cannot be used to change server configuration
5) Cannot be used with XML FOR clause
6) Cannot have transaction within function

Stored Procedure
-----------------
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won’t return Table Variables
4) you can not join SP
5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP 


Hope this will be helpful and if there's any correction let me know.

Regards 

Shikhar 

Delete Duplicate Records

Takeaway: Removing duplicate records from tables is sometimes an arduous task, especially if the source table does not contain a primary key field. Here’s how you can take advantage of a new SQL Server 2005 feature to remove duplicates quickly and efficiently.
----------------------------------------------------------------------------------------------------

Duplicate Records

Duplicate records can occur numerous ways, such as loading source files too many times, keying the same data more than once, or from just bad database coding. Having a primary key on your table (and you always should have one) can will in the removal of the duplicate records, but even w/ a primary key it is never a fun task to have handed to you to complete.
In today’s example I will demonstrate how you can use a common-table expression (CTE) in 2005 to easily remove duplicate entries from a table, even when the table does not contain a primary key field. The script below creates the SalesHistory table, and loads 10 records into the table. Two of these records are duplicate entries, which I am defining as having the same values for the Product, SaleDate, and SalePrice fields. In the real world, these may not be duplicate records at all, but for our example we’ll assume that they are.

IF OBJECT_ID('SalesHistory') IS NOT NULL

DROP TABLE SalesHistory

CREATE TABLE [dbo].[SalesHistory]

(

[Product] [varchar](10) NULL,

[SaleDate] [datetime] NULL,

[SalePrice] [money] NULL

)

GO

INSERT INTO SalesHistory(Product, SaleDate, SalePrice)

SELECT 'Computer','1919-03-18 00:00:00.000',1008.00

UNION ALL

SELECT 'BigScreen','1927-03-18 00:00:00.000',91.00

UNION ALL

SELECT 'PoolTable','1927-04-01 00:00:00.000',139.00

UNION ALL

SELECT 'Computer','1919-03-18 00:00:00.000',1008.00

UNION ALL

SELECT 'BigScreen','1927-03-25 00:00:00.000',92.00

UNION ALL

SELECT 'PoolTable','1927-03-25 00:00:00.000',108.00

UNION ALL

SELECT 'Computer','1919-04-01 00:00:00.000',150.00

UNION ALL

SELECT 'BigScreen','1927-04-01 00:00:00.000', 123.00

UNION ALL

SELECT 'PoolTable','1927-04-01 00:00:00.000', 139.00

UNION ALL

SELECT 'Computer','1919-04-08 00:00:00.000', 168.00

Now that I have some duplicate records loaded up, I can start writing my routine to remove them. The trick with removing duplicates is to find a way to delete them from your source table, rather than grouping all of the records in the source table together and creating a new table w/ the new entries. Deleting from the source usually requires a way to uniquely identify one of the unique records and then remove it. With some crafty TSQL, this is a relatively easy task to do when a primary key defined on the table. Luckily, the new CTE feature in SQL Server 2005 makes it very easy to remove these duplicates, with or without a primary key.
The script below defines my CTE. I am using a windowing function named DENSE_RANK to group the records together based on the Product, SaleDate, and SalePrice fields, and assign them a sequential value randomly. This means that if I have two records with the exact same Product, SaleDate, and SalePrice values, the first record will be ranked as 1, the second as 2, and so on.

;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking)

AS

(

SELECT

Product, SaleDate, SalePrice,

Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate,SalePrice
ORDER BY NEWID() ASC)

FROM SalesHistory

)

DELETE FROM SalesCTE

WHERE Ranking > 1

Because a CTE acts as a virtual table, I am able to process data modification statements against it, and the underlying table will be affected. In this case, I am removing any record from the SalesCTE that is ranked higher than 1. This will remove all of my duplicate records.
To verify my duplicates have been removed, I can review the data in the table, which should now contain 8 records, rather than the previous 10.

SELECT * FROM SalesHistory

Be Creative

Common-table expressions are a very useful new feature in SQL Server 2005. You can use them for recursive queries, removing duplicates, and even simple looping procedures. Explore this neat new feature to find some creative ways to solve some of your everyday database problems.

Dynamic Menu Creation In Windows (c#)

Introduction

· Menus make it easy to access the functionality and take less space and make your application look more organized.

· Menus contain top – level items and drop – down items.

o Top – level items: all visible items are top-level menus

o Drop – down items: non – visible items are dropdown menus and also call it as child menus of a top – level menu.

Classes required to create a Menu

1. MenuStrip:

· This control is like a container will hold all the top – level menus going to be created.

· This control will be available under “System.Windows.Forms” namespace.

Decalration of a MenuStrip control

Import the namespace – using System.Windows.Forms

// Declare the menustrip object.

MenuStrip menuItems = new MenuStrip();

2. ToolStripMenuItem :

· This class is used to create menu items like top – level or drop – down menus going to be created.

· It can also represent a submenu of another ToolStripMenuItem object. ToolStripMenuItem objects are viewed by the user, whereas a MenuStrip object simply establishes a container where menu items appear.

· This class will be available under “System.Windows.Forms” namespace.

Decalration of a MenuItem class

Menu can be created using a Text or Image or both.

// Take a ToolStripMenuItem to add the menu item with string.

ToolStripMenuItem menuItem = new ToolStripMenuItem(“File”);

Or

// Take a ToolStripMenuItem to add the menu item with Image.

ToolStripMenuItem menuItem = new ToolStripMenuItem(new Bitmap(“path of the image to load”));

Or

// Take a ToolStripMenuItem to add the menu item with Strng and Image.

ToolStripMenuItem menuItem = new ToolStripMenuItem((“File”, new Bitmap(“path of the image to load”));

These are the main classes to create any kind of menu.

Using this information will know how to create dynamic menus.

Program: Going to create dynamic simple menus

Output: looks like this

File Edit Help - Top – level menus

New Copy AboutMe - drop – down menus

Open Cut ContactUs

Exit Pate Help

Step-By-Step code explanation to create a dynamic menu

1. Define menus required.

Note: There will be so many ways to approach declaring the menu items but here I used by taking dictionary object.

// Create Main menu and child menus in a ditcionary object as key pair values.

Dictionary<string, string[]> displayMenus = new Dictionary<string, string[]>();

// Define the child menus based on the each main menu on the basis of key.

displayMenus.Add(“File”, new string[] { “New”, “Open”, “Exit” });

displayMenus.Add(“Edit”, new string[] { “Copy”, “Cut”, “Paste” });

displayMenus.Add(“Help”, new string[] { “AboutMe”, “ContactUs”, “Help” });

2. Write a function to create menus by taking the dictionary object as parameter.

///

/// This method will create a menu based on the menus

/// and it’s submenus defined.

///

///

/// Menus and it’s submenus in a dictioanry object

///

///

/// Returns a collection of a toolstripmennu items.

///

private List<ToolStripMenuItem> CreateMenu(Dictionary<string, string[]> displayMenus)

{

// Declare ToolStripMenuItem object.

List<ToolStripMenuItem> menuItems = new List<ToolStripMenuItem>();

// Loop through all main menus.

foreach (KeyValuePair<string, string[]> menu in displayMenus)

{

// Take a ToolStripMenuItem to add the menu item.

ToolStripMenuItem menuItem = new ToolStripMenuItem(menu.Key);

// Set a name to the menu.

menuItem.Name = menu.Key;

// Create child menu items for a menu item.

this.CreateChildMenus(menuItem, menu.Value);

switch (menu.Key)

{

case “File”:

case “Edit”:

// This is by default.

// menuItem.Alignment = ToolStripItemAlignment.Left;

break;

case “Help”:

menuItem.Alignment = ToolStripItemAlignment.Right;

break;

}

// Add each menu item to the menu strip item.

menuItems.Add(menuItem);

}

return menuItems;

}

3. Write a function to create child menus by taking the parent menu and the child menus going to be created.

///

/// Thsi method will create a child menus of a menu item.

///

///

/// Parent menu item to add child menu items.

///

///

/// Child menu items going to be created.

///

private void CreateChildMenus(ToolStripMenuItem parentMenuToAddChildMenus, string[] childMenus)

{

// Loop through all child menus.

foreach (string childMenu in childMenus)

{

// Take a ToolStripMenuItem to add the menu item.

ToolStripMenuItem childMenuItem = new ToolStripMenuItem(childMenu);

// Set a name to the menu.

childMenuItem.Name = childMenu;

// Hnadle the event for the menu created.

childMenuItem.Click += new EventHandler(ChildMenu_Click);

// Add each child menu to its parent menu item.

parentMenuToAddChildMenus.DropDown.Items.Add(childMenuItem);

}

}

4. Write an event handler common to all the menu items.

///

/// This method will handle the click event for each menu.

///

/// Tool strip menu item.

/// Event args.

private void ChildMenu_Click(object sender, EventArgs e)

{

ToolStripMenuItem sourceMenuItem = (ToolStripMenuItem)sender;

string selectedMenu = string.Empty;

// Selected menu item

switch (sourceMenuItem.Name)

{

case “New”:

// Required statements here.

selectedMenu = sourceMenuItem.Text;

break;

case “Open”:

// Required statements here.

selectedMenu = sourceMenuItem.Text;

break;

case “Exit”:

// Required statements here.

selectedMenu = sourceMenuItem.Text;

break;

case “Edit”:

// Required statements here.

selectedMenu = sourceMenuItem.Text;

break;

case “Copy”:

// Required statements here.

selectedMenu = sourceMenuItem.Text;

break;

case “Paste”:

// Required statements here.

selectedMenu = sourceMenuItem.Text;

break;

case “AboutMe”:

// Required statements here.

selectedMenu = sourceMenuItem.Text;

break;

case “ContactUs”:

// Required statements here.

selectedMenu = sourceMenuItem.Text;

break;

case “Help”:

// Required statements here.

selectedMenu = sourceMenuItem.Text;

break;

}

if (!string.IsNullOrEmpty(selectedMenu))

{

MessageBox.Show(string.Concat(

selectedMenu,

” feature is under development “));

}

}

5. add the created menu items collection to the MenuStrip object

Conclusion:

· This is the procedure to create dynamic menus in windows application.

· If you want to create your custom type dynamic menus then modify the code according to your needs in a dictionary object as defined in step1.

Note: This is just a sample code to guide you how a menu can be created.