Tag Archives: New feature of Sqlserver 2008

Table value parameter new feature in SQLSERVER 2008.

Posted in Procedure, Sqlserver, Tsql by

Today i will explain very  nice and useful feature of the Sqlserver 2008.In the past i was facing many problem in reading and writing data in the Database from .Net environment for bulk insert and delete.I personally use XML to read the data from vb.net for implement bulk insert and delete.
After understanding this feature this thing is very easly done in future.
Below is the example of,
how we can pass Table as parameter to Procedure?

— Step 1
— Create table Student

CREATE TABLE [dbo].[Student](
[Student_ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](80) NOT NULL,
[City] [varchar](100) NOT NULL
) ON [PRIMARY]

GO

Step 2

— Insert the Rows in the Student Table
Insert into Student
values(‘Jhon’,’NewYork’)
Insert into Student
values(‘Mac’,’Paris’)
Insert into Student
values(‘Rahul’,’Delhi’)
Insert into Student
values(‘Jay’,’Mumbai’)
Insert into Student
values(‘Jack’,’London’)

select * from Student

Step 3
— very important
— Create the StudentTabelType as userdefine datatype of Table
CREATE TYPE [StudentTableType] AS TABLE
(
[Name] [varchar](80) NOT NULL,
[City] [varchar](100) NOT NULL
)
GO

— Create the Procedure which get the Table as input parameter
Create PROCEDURE StudentDetail(@StudentTable StudentTableType READONLY)
AS
BEGIN
INSERT INTO Student
SELECT * FROM @StudentTable

— You can create Tabel variable like this
–DECLARE @StudentTab StudentTableType
–insert into @StudentTab
–values (‘Rajesh’,’Bhopal’)
–insert into Student
–select * from @Studenttab
END
GO
You must pass Table variable as read only you can not prform
any DML command on this pass Table.
Like that you can pass table in the procedure and use for the further manipulation.

Now, one of the important thing is how to pass from .net environment.

Declar the datatable with two columns name and city.
Declare DTStudent as new datatable
DTstudent.Columns.add(“Name”)
DTstudent.Columns.add(“City”)

Insert some Records in this DTStudent Table.
Create Connection and command object and set all it’s property to establish connection to sqlserver.

Add parameter to command
Dim sqlcommad as new SqlClient.SqlCommand
Use the Structured datatype for the parameter and set the datatable to the
value of the parameter.

sqlcmd.Parameters.Add(“StudentTable”, SqlDbType.Structured).value = StudentTable

This feature is very useful in bulk insert and delete of data.

I hope this article is useful to you,give me feed back on this article.