Differences between Stored Procedures and Functions

Stored Procedures	Function
Use EXEC or EXECUTE	Used with Select statement
Return output parameter	Not returning output parameter but returns Table variables
Cannot join SP	We can join UDF
Can be used to change server configuration.	Cannot be used to change server configuration.
Can be used with XML FOR Clause	Cannot be used with XML FOR clause
Can have transaction within SP	Cannot have transaction within function
SP takes input, output parameters	Function takes only input parameters.
SP cannot be called directly into DML statements	Functions can be called directly into DML statements.
SP may return one or more values through parameters or may not return at all	A FUNCTION is always returns a value using the return statement
SP can return multiple values (max 1024).	A Function returns 1 value only.
SP returns always integer value by default zero.	Whereas function returns type could be scalar or table or table values
SP is precompiled execution plan	A function can call directly by SQL statement like select func_name from dual
SP cannot be used in SQL queries	A Function can be used in the SQL Queries
We can use try catch statements in stored procedures	Functions we can not use try catch statements.
We can use insert,delete,update and create statements in SP.	We can not use insert,delete,update and create statements in functions.
Stored procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section.	UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section

How can debugging in SQL Server 2008 ?

How can debugging in SQL Server 2008 ?

Step1. Debug user define function in sql 2008

Step2. SELECT DBO.Ordernumber()

As per below screen step----

User Define function for Unique ID

CREATE  FUNCTION [dbo].[Ordernumber]()

RETURNS varchar(20)

AS

BEGIN

DECLARE @_ggmvalue varchar(30)

DECLARE @_ggmvalue1 varchar(30)

DECLARE @_ggmvalue2 varchar(20)

DECLARE @_ggmmilisec varchar(5)

DECLARE @lengthmilisec varchar(5)

DECLARE @_TtmPrefix varchar(16)

DECLARE @_TtmSufix varchar(16)

DECLARE @_TtmSufix1 varchar(16)

DECLARE @_TtmId varchar(22)

DECLARE @length int

select @_ggmvalue1=convert(varchar(10),getdate(),112)+convert(varchar(10),getdate(),108)

select @_ggmmilisec=convert(varchar(10),getdate(),120)

set @length=Len(@_ggmmilisec)

if(@length<2)

begin

set @_ggmmilisec='000'+@_ggmmilisec

end

if(@length<3 and @length>=2)

begin

set @_ggmmilisec='00'+@_ggmmilisec

end

if(@length<4 and @length>=3)

begin

set @_ggmmilisec='0'+@_ggmmilisec

end

if(@length>=4)

begin

set @_ggmmilisec=@_ggmmilisec

end

select @_ggmvalue=@_ggmvalue1+@_ggmmilisec

select @_ggmvalue2= Replace(@_ggmvalue,':','')

select @_TtmSufix=RandNumber from vRandNumber

select @_TtmSufix1= Replace(@_TtmSufix,'.','')

select @_TtmPrefix=Substring(@_ggmvalue2,3,18)

set @_TtmId=@_TtmPrefix+convert(varchar(6),@_TtmSufix1)

return @_TtmId

END

** Create view for RandNumber

CREATE VIEW [dbo].[vRandNumber]

AS

SELECT RAND() as RandNumber

GO

Different method for insert data in Table (Row Value Constructor) SQL 2008

Different method for insert data in Table (Row Value Constructor) SQL 2008

In SQL Server table Insert, Delete and Update data as per Data Manipulation Language. We have insert large number of data in table by insert query.SQL Server 2008 provide new method for insert data in SQL tables.

CREATE TABLE [dbo].[test_table](

          [sno] [int] NOT NULL,

          [name] [nvarchar](50) NULL,

          [BILL] [nchar](10) NULL

) ON [PRIMARY]

GO

Method 1

Add 3 rows of data using a traditional ANSI insert SQL statement as shown below

insert into test_table (sno ,name ,BILL) values (1 , 'rohan' ,345)

insert into test_table (sno ,name ,BILL) values (2 , 'vipin' ,745)

insert into test_table (sno ,name ,BILL) values (3 , 'chandra' ,845)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

sno	name	BILL
1	rohan	345
2	vipin	745
3	chandra	845

Method 2

insert into test_table select 4 , 'rohan1' ,345

insert into test_table select 5  , 'vipin1' ,745

insert into test_table select 6  , 'chandra1' ,845

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

sno	name	BILL
1	rohan	345
2	vipin	745
3	chandra	845
4	rohan1	345
5	vipin1	745
6	chandra1	845

Method 3

Add 3 rows of data using INSERT SQL Statement with a SELECT and UNION clause to insert data.

insert into test_table

select 7 , 'rohan1' , 150000.00

union select 8 , 'ravi' , 250000.00

union select 9 , 'raj' ,120000.00

(3 row(s) affected)

sno	name	BILL
1	rohan	345
2	vipin	745
3	chandra	845
4	rohan1	345
5	vipin1	745
6	chandra1	845
7	rohan1	150000.00
8	ravi	250000.00
9	raj	120000.00

Method 4 (Row Value Constructor SQL 2008)

Add 3 rows of data using INSERT SQL Statement with a Row Value Constructor.

insert into test_table (sno ,name ,BILL) values

(10 , 'amit' , 150000.00),

(11 , 'nitin' , 250000.00),

(12 , 'cp' , 120000.00)

(3 row(s) affected)

sno	name	BILL
1	rohan	345
2	vipin	745
3	chandra	845
4	rohan1	345
5	vipin1	745
6	chandra1	845
7	rohan1	150000.00
8	ravi	250000.00
9	raj	120000.00
10	amit	150000.00
11	nitin	250000.00
12	cp	120000.00

Difference between Shallow and Deep copy

Difference between Shallow and Deep copy:

Shallow Copy:

Shallow copies duplicate as little as possible. A shallow copy of a collection is a copy of the collection structure, not the elements. With a shallow copy, two collections now share the individual elements.

Shallow Copy points to the same location in memory as Source does.

Value type:  Copy of the field is performed

Reference type: The reference is copied NOT the object so original object

and its Shallow Copy refer to the same object.

Deep Copy:

Deep copies duplicate everything structure and element both. A deep copy of a collection is two collections with all of the elements in the original collection duplicated.

Deep Copy points to a different location in memory, but the elements are the same.

Value type: Copy of the field is performed

Reference type: The object itself is copied, NOT only the reference so original object and its Deep Copy refer to two different object.

Example:

var realdata = { FName="ask", LName="dotnet" };

var shallow = ShallowCopyOf(realdata);

var deep = DeepCopyOf(realdata);

realdata.LName = ".net";

WriteLine(realdata.LName); // outputà .net

WriteLine(shallow.LName); // outputà .net

WriteLine(deep.LName); // outputà dotnet

How can set GRID header name from database table in WIN FORM

By this code we can set Grid header name from our database table daynamicaly.    

private void bindDataExpensesNew()

        {

            DataTable oldTable = new DataTable();

            DataTable dtdataExpns = new DataTable();

            oldTable = ObjBll.GetItemtest();

            for (int i = 2; i <= oldTable.Rows.Count+2; i++)

              

            {

                dataGridView1.Columns[0].HeaderText = "To Date";

                dataGridView1.Columns[1].HeaderText = "From Date";

                for (int j = 0; j <= oldTable.Rows.Count; j++)

                {

                 dataGridView1.Columns[j].HeaderText =oldTable.Rows[j]["name"].ToString();

              

                }

            }

          

        }

Page load:

private void Form2_Load(object sender, EventArgs e)

        {

            bindDataExpensesNew();

        }

BLL file code:

public DataTable GetItemtest()

        {

            string str = "select name from table;

            dt = ObjDAL.GetResultFromSqlQur(str);

            return dt;

        }

DELETE, TRUNCATE and RESEED Identity IN SQL SERVER

TRUNCATE ACTION IN SQL

1.select * from test_table

sno	name
1	chandra
2	vipin
3	raghu
4	taslim
5	ravi
6	manju
7	neha
8	neeraj
9	mohit
10	ashish

2.TRUNCATE table test_table

Sno    name

No data in table.

After truncate table always row start in table new sno.

3. insert into test_table values ('chandra')

insert into test_table values ('ravi')

insert into test_table values ('vipin')

insert into test_table values ('neha')

sno	name
1	chandra
2	chandra
3	ravi
4	vipin
5	neha

DELETE ACTION IN SQL

4.delete from test_table where sno=4

sno	name
1	chandra
2	chandra
3	ravi
5	neha

After delete new row data start from deleted sno (5)+1 = 6

5. insert into test_table values ('vipin')

sno	name
6	vipin
2	chandra
3	ravi
5	neha

RESEED ACTION IN SQL

6.DBCC CHECKIDENT ('test_table', RESEED, 1)

When run this query in SQL we have one message from SQL side:

Checking identity information: current identity value '6', current column value '1'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This new value logic is Reseed Value + Interval Value – in this case it will be 1+1 = 2.

sno      name

6          vipin

2          chandra

3          ravi

2          neha (new value insert in table)

5          neha

3          rajesh (new value insert into table)

7.DBCC CHECKIDENT ('test_table', RESEED, 10)

Checking identity information: current identity value '3', current column value '10'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

8.insert into test_table values ('reseed data')

sno	name
6	vipin
2	chandra
3	ravi
2	neha
5	neha
3	rajesh
11	reseed data

New Row insert with (10+1=11 sno becoues our cuurent value is 10)

9.delete from test_table where sno=11

sno	name
6	vipin
2	chandra
3	ravi
2	neha
5	neha
3	rajesh
12	reseed data

We have delete sno 11 and then insert new data then start sno 12.

 10.DBCC CHECKIDENT ('test_table', RESEED, 1000)

sno      name

6          vipin

2          chandra

3          ravi

2          neha

5          neha

3          rajesh

12        reseed data

1001    cp