procedure vs functions in sql server... 7 differences

Here are 7 difference i found between sql server procedure and user defined functions.

Procedure: Procedures are mainly used to process the tasks more aligned towards data stored in Database.

1. Doesn't need to return values but can return value, return statement does not have any effect .

2. Stored are compiled inside database and has execution plan ready.

3. Can affect the state of database using DML and TCL operations.

4. Cannot be invoked from SQL statements e.g. SELECT.

5. Select statement returns data to client.

6. It is allowed to create database objects. temporary and permanent objects.


Function : Functions are preferrably used to compute/calculate values.

1. Must return at least one output parameter.Can return more than one parameter using OUT argument.

Error message we get "The last statement included within a function must be a return statement.".

2. Parsed and compiled at run time.

3. Cannot affect the state of database, Means it can not have UPDATE,DELETE kind of statements

4. Can be invoked from SQL statement e.g. SELECT.

5. A simple select statement does not return data to client.
message we get is "Select statements included within a function cannot return data to a client."

6. Object can't be create inside UDF. Like table, views etc

ShortDatePattern with MMM

I wanted date printed with the given culture format but to print month name instrad of month number like Jan instead of 01 in en-us, I tried this with few combination and DateTimeFormat along with RegEx came into rescue. here is the code.

DateTime date = DateTime.Today; // Take a date to display in given format.
CultureInfo culture = CultureInfo.GetCultureInfo("en-us"); // get a random calture.
string datePattern = culture.DateTimeFormat.ShortDatePattern; // get short date format

datePattern = Regex.Replace(datePattern, "(?i)[m]+", "MMM"); // replace Month natation for MMM to get 3 char long month name
Console.WriteLine(date.ToString(datePattern,culture)); // print date in given format

datePattern = Regex.Replace(datePattern, "(?i)[m]+", "MMMM"); // replace Month natation for MMMM to get full month name.
Console.WriteLine(date.ToString(datePattern, culture));

Cheers!!!
Pradeep

what a present surprise Hashtable and pradeep

While searching for something on google, to looked my post i ranked 1st on the google search. try
hashtable + c# in google or (click here). I feel now i should devote time to develop, teaching and writing skills :)


-- pradeep

Who am i ?

I am not talking about movie of Jaki Chain with the same name,

Who am i?
Why am i here?
and what will happen to me after my death?
what is was before taking birth on earth ?

who can answer these queries, i am not able to find the solution.

--pradeep

do not forget to Remove DataRowFilter value

If you are playing with datatable`s DefaultView view then don`t forget to change it to default mode by setting RowFilter property of DefaultView to string.Empty, otherwise you may get in to trouble.
Same happened with my one of colleague, and after 2 days debugging she came to me.

== here is full code

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace DatatableTest
{
public class DataTableOperationWrapper
{
DataTable dtTable;

public bool ConstructDataTableAndFillData()
{
if (dtTable == null)
{
CreateTable();
}
else
{
dtTable.Rows.Clear();
dtTable.AcceptChanges();
}

DataRow dr = dtTable.NewRow();

for (int i = 0; i <>
{
dr["Name"] = "PersonName_" + i.ToString();
dr["Age"] = i; dtTable.Rows.Add(dr);
dr = dtTable.NewRow();
}

dtTable.AcceptChanges();
return true;
}

private void CreateTable()
{
dtTable = new DataTable();
dtTable.Columns.Add("Name", typeof(string));
dtTable.Columns.Add("Age", typeof(int));
}

public void ApplyDataViewRowFilter(string fileterCondition)
{
if (dtTable == null)
{
ConstructDataTableAndFillData();
}
dtTable.DefaultView.RowFilter = fileterCondition;
}
public DataTable GetResult()
{
if (dtTable == null)
{
new ApplicationException("DataTable is null");
}
return dtTable;
}
///

/// Remove the filters applied as DataRowFilter condition and get all the data from datatable.
///

public void RemoveDataViewRowFilter()
{
if (dtTable == null)
{
new ApplicationException("DataTable is null");
}
dtTable.DefaultView.RowFilter = string.Empty;
}
}
}

== till here

i just tried to set RowFilter value to string.Empty and it worked.
(Don`t get into design of class mentioned above.)

Thanks
Pradeep

World`s 3rd and 4th most poluted cities are in India

I just shocked, when i come to know India houses world`s 2 most polluted cities
these are Vapi in Gujrat 4th Rank
and Sukinda in Orissa 3rd Rank

here is location details http://www.mibazaar.com/pollutedcities.html
What is conclustion : We have to ask people and government to look into the matter seriously, otherwise surrounding areas mey get affected.

Thanks for your precious time...
Pradeep Tiwari
same post could be found at
http://happyrepublicdayindia.blogspot.com/

Health - Important Tips

Answer the phone by LEFT ear.
Do not drink coffee TWICE a day.
Do not take pills with COOL water.
Do not have HUGE meals after 5pm.
Reduce the amount of OILY food you consume.
Drink more WATER in the morning, less at night.
Keep your distance from hand phone CHARGERS.
Do not use headphones/earphone for LONG period of time.
Best sleeping time is from 10pm at night to 6am in the morning.
Do not lie down immediately after taking medicine before sleeping.
When battery is down to the LAST grid/bar, do not answer the phone as the radiation is 1000 times.
Forward this to those whom you CARE about!


---Thanks and care
Pradeep