jump to navigation

A thorough Introduction to Integration Services March 3, 2009

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags: , ,
add a comment

In this rather huge post I will try to explain and give valuable references when appropriate, regarding the use and functionality of Integration Services or SSIS for short. There will be other posts following regarding SSIS.

I will be using SQL Server 2008 for the various examples but you can use SQL Server 2005. It will not be a problem.

I will try to explain the main concepts of SSIS. More specifically I will try to talk about

  • Tasks integrated Services are best suited for
  • What the major components of SSIS are
  • The techniques that one can use to build,debug and deploy transformations with Business Intelligence Development Studio

SSIS is an automation tool-component that is part of SQL Server 2005 and SQL Server 2008. It replaces DTS which has been a set of tools that were useful to DBAs to import, export, and transform data. DTS was included in all previous versions of SQL Server up to 2000.SSIS is only available in the “Standard” and “Enterprise” editions.

It is used for

  • Moving data from one database to another database
  • Copy database to an excel file or a text file
  • Extract data from a data source, manipulate it,transform it and import it in a database
  • Converting between various data types
  • Merging columns into new or existing columns

The type of operation that we perform with SSIS is called ETL.

To access information related to SSIS from the msdn site click here .

For a more complete and sound definition you can click here and see what Wikipedia says about SSIS.

So to recap these are the main tasks that SSIS are used for:

  • Merging data from multiple sources. I know many people out there, will claim that they can do that with writing code in a .net language. Yes of course one can do that. But it takes more time and one will need lots of time for debugging and testing purposes. SSIS provide wizards and components which are configured to do exactly that.
  • Populate data warehouses and data stores. This means that SSIS is a great tool for getting data from a OLTP database and insert it into OLAP data store
  • Cleaning and validating the data in the database
  • Build business intelligence logic into data transformation
  • Automate administrative tasks like database back up, delete,copy database objects

Now that all the declarations are out of the way, let’s review the necessary tools for creating,debugging,deploying and managing SSIS packages. The two main tools are

  • Business Intelligence Development Studio
  • SQL Server Management Studio

In order to create a SSIS package you must create a new SSIS integration project.

In order to create the project you need to fire up BIDS(Business Intelligence Development Studio). Then select New Project and from the Project type window select “Business Intelligence Projects”. From the templates window select the Integration Services Project. Have a look at the picture below.

bids

When you click OK in the New Project window, you will have in your screen something similar like the picture below.

bids1

In this simple project we have just created, there are 4 folders in it anda a sample Package called Package.dtsx.

A package is the main container for tasks that are part of a complete unit of work.

A Package contains objects that you can simply drag and drop from the Toolbox window and processes that control the sequence of those objects. The objects can be

  • Control flow objects which are commonly referred as tasks, like copying files and sending emails
  • Data flow objects are called transformations. You extract data using data sources, you transform tha data (in memory) using data transformations and load data using data destinations.

There are 3 main processes. These Processes are represented by the 3 different tabs in the Package.dtsx[Design].

  • Control Flow. The Control Flow tab allow us to drag and drop control flow items from the Toolbox to the designer surface and then connect them together.
  • Data Flow. Their mission is to retrieve data from a data source and save it to a target destination
  • Event Handlers. This allow us to perform various tasks at specific points in time during the lifetime of a package.

I will start a new practical example to demonstrate the above topics. I need a database to do that. I will use the AdventureWorksLT database.SQL Server 2005-2008 does not install sample databases by default due to security reasons. We must download them and install them separately. You can download this free database from the codeplex website. Codeplex is the project hosting site for Microsoft SQL Server Samples and Community Projects.The down-loadable file, will  be a .msi file that you need to run and after successfully doing that, the .mdf and .ldf files will be placed somewhere on your hard disk. Then you can attach them to your local instance of your SQL Server from SQL Server Management Studio.

  • For those that have Sql Server 2005 installed they need to go and download the file ,”AdventureWorksLT.msi”, from here
  • For those that have Sql Server 2008 installed they need to go and download the file,”SQL2008.AdventureWorks_All_Databases.x86.msi”, from here

So I assume that you have downloaded the correct version of the AdventureWorksLT database and attached it to the local instance of your SQL Server. In this example I want to

  • Get data from Customer table in my AdventureWorksLT database
  • Make some transformations on the retrieved rows of the Customer table. e.g combine two columns of the table into a new column. Take FirstName and LastName columns and combine them into a new one.
  • Save the data to a different format in a .xls file.

1) Start BIDS (Business Intelligence Development Studio)

2) Create a new business intelligence project (Integration Services Project) and call it “TranformCustomers”

3) Drag and Drop a Data Flow Task onto the designer surface.

4) Double click on it and you will immediately be transferred from the Control Flow tab to the Data Flow tab.

5) Go to your toolbox and drag and drop on the design surface(Data Flow tab) a OLE DB Source component.

6) Double click the component and in the window that appears (OLE DB Source Editor) . Click the button New

7) Click New again to new window that appears titled Configure OLE DB Connection Manager

8) In the next window that will appear titled Connection Manager find the name of your server

9) Then select the AdventureWorksLT database, Hit the “Test the Connection” Button and click “OK” and then click “OK” one more time.

10) In the OLE DB Source Editor window make sure that the option Table or View is selected.

11) Then select the SalesLT.Customer table from the dropdown.

12) Hit the Preview button if you want and click “OK”

13) Drag and drop a Derived Column component onto the Designer Surface.

14 ) Drag the green arrow from the OLE DB Source component to the Derived Column component until a connection is established.

15) Double click on the Derived Column component. A new window, Derived Column Transformation Editor, appears.

16) In this new window under the Derived Column Name column, type a name for the new derived column.

17) In the Expression type FirstName + ” ” + LastName and then click OK.

If you have done everything correctly so far you should have something that looks similar with the picture below

bids3

18) The next step is to define where our transformed data will be located. Drag and drop an Excel Destination Component onto the designer surface.

19) Drag the green line-arrow from the  Derived Column component to the Excel Destination component  until a connection is established.

20) Minimise all the windows. Start Microsoft Excel 2003 or 2007 and create an empty workbook. Name it Customer.xls and save it on your desktop. Open the workbook and name the first worksheet Customer Data.

21) Double click the Excel Destination component. The Excel Destination Editor window appears.

22) Click on the New button and from the Excel Connection Manager window click Browse and then navigate to the Customer.xls and click Open and then OK.

23) Hit the New Button(Excel Destination editor window), next to the Name of the excel sheet:

24) In the Create Table window change the CREATE TABLE `Excel Destination` to CREATE TABLE `Customer Data` and click OK.

25 ) Hit the Preview button to have a look at the columns.

26) Click OK to exit the Excel Destination editor window and save your changes to the project

27) Run your project by hitting F5

28) Your package should execute fine and you should have a picture similar like the one below

bids4

29) Open the Customer.xls file from your desktop and see the transformed data in it.

Now let’s see another way of  creating packages using the Import / Export Wizard.

We will demonstrate how to create a package with a Import / Export Wizard with an example. In this example I want to copy certain tables from one database that resides in my local instance of SQL Server to a new database that I will create and also resides in my local instance of SQL Server. The database will be AdventureWorksLT.

1) Start BIDS (Business Intelligence Development Studio)

2) Create a new business intelligence project (Integration Services Project) and call it “Import/Export”

3) On the SSIS Packages folder right-click and select SSIS Import and Export wizard.

4) In the wizard that pops up click Next.In this window select the data source. In this example you should use the local intance of your SQL Server and then select the AdventureWorksLT database. Then click Next.

5) In the next step of the wizard, select the destination. In this example it will be a new database in the same server.

6) Hit the New button and in the new window that appears just type “Mynewdb” and hit the OK button and then click the Next button.

7) Select (should be selected by default) the first option in the next step of the wizard.Click Next.

8) In the next step of the wizard select as many tables of the database that you want copied to the new database.

9) Click the Next button and then click the Finish button.

10) if you go back to your project in the BIDS, you will see a new package that was created automatically for us and called Package1.dtsx. We did not have to do anything manually only follow the simple steps of the wizard.

11) Run the package by hitting F5.

12) Check that the destination database has all the data you specified in the steps of the wizard.

That is all for now. It is not difficult, is it?

Add to FacebookAdd to NewsvineAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to Ma.gnoliaAdd to TechnoratiAdd to Furl

Introduction to creating, modifying, executing stored procedures February 28, 2009

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags: , ,
1 comment so far

I have not been talking too much about databases or database design in this blog. There are some posts but this is not a blog about databases, database design or RDBMS. A brilliant blog for SQL Server databases is SQL Authority .Here you can find hundreds of articles and scripts for people that use SQL Server as their main RDBMS.

In this post I am going  to talk about stored procedures in conjunction with an SQL Server database.

I always try to write posts based on my day to day experience with people who attend the seminars I happen to be teaching. Some people are confused on why we need them, what they are and what they actually do.

As you know with Visual Studio 2008 and the new framework version (3.5 SP1 included) we have many different models-ways-methodologies that we can access a relational database, or the relational world, as I like to call it.

We have LINQ for example which we can use against any data source.  With LINQ we can express efficient query behavior in a programming language of our choice,  transform the query results into whatever format we desire, and manipulate the results.
LINQ to SQL is an object relational mapping  implementation that allows us to model a relational database using .NET classes.  We can query the database using LINQ, as well as update-insert-delete data from it.
LINQ to SQL designer provides an easy way to model and visualize a database as a LINQ to SQL object model.

So many people thought, “fantastic, I do not have to know anything about the relational world, database design,T-SQL and database objects.”

Well that is a huge mistake. For me anyone who is using the .NET platform to create data-driven applications must understand database design and how to manipulate the main database objects like views,triggers,stored procedures,tables,indexes.

You need that because many data driven applications out there do not use LINQ or Entity framework and there is a big chance that even if you create your application with LINQ you will need to utilise stored procedures as LINQ to SQL fully supports transactions, views, and stored procedures.

So you cannot be agnostic regarding the relational world. Having said that, please do learn all the new things that ship with the latest versions of .net framework and refer to data access.

  • I will use SQL Server 2008 Standard edition to write,manipulate and execute some stored procedures. You can use SQL Server 2005 or any of the free SQL Server Express editions
  • I will use the AdventureWorksLT database.SQL Server 2005-2008 does not install sample databases by default due to security reasons. We must download them and install them separately. You can download this free database from the codeplex website. Codeplex is the project hosting site for Microsoft SQL Server Samples and Community Projects.The down-loadable file, will  be a .msi file that you need to run and after successfully doing that, the .mdf and .ldf files will be placed somewhere on your hard disk. Then you can attach them to your local instance of your SQL Server from SQL Server Management Studio.
  • For those that have Sql Server 2005 installed they need to go and download the file ,”AdventureWorksLT.msi”, from here
  • For those that have Sql Server 2008 installed they need to go and download the file ,”SQL2008.AdventureWorks_All_Databases.x86.msi”, from here

I do not know if you are familiar with the sample databases that are available for us to download and use for free.The AdventureWorks database supports standard on-line transaction processing scenarios for a fictitious bicycle manufacturer (Adventure Works Cycles). Scenarios include Manufacturing, Sales, Purchasing, Product Management, Contact Management, and Human Resources.AdventureWorksLT (the database we are going to be using)  is a highly simplified and somewhat denormalized version of the Adventure Works OLTP database focused exclusively on a simple sales scenario. For more information for this database click here.

Please before you read more on this post, familiarise yourself  with this database.Have a look at the tables and the relations between the tables. It is not difficult at all.

1) Launch SQL Server Management Studio

2) Connect to the Local Instance of your SQL Server

3) Make the AdventureWorksLT database as the current database

4) Open a new query window and type the following simple T-SQL code that follows. Hit the execute button

select * from SalesLT.Customer where CustomerID=21

That will return a row of data from the Customers table. Have a look at the results pane, to see for yourself.

If I want to execute the above T-SQL code many times, I should create a stored procedure. So we can take the code above and create a stored procedure. Type the following

CREATE PROCEDURE uspGetCustomers
AS
select * from SalesLT.Customer where CustomerID=21

Hit the execute button. Congratulations, you have created your first stored procedure.

If you click on the Programmability ->Stored Procedures you will see the new database object, you have just created.

5) If you want to execute your new stored procedure, you simply type in the query window

exec uspGetCustomers

and hit the execute button. In the results pane you will get one row of data.

6) If you do not want the message that shows the count of the number of rows affected, in the Messages pane (1 row(s) affected), by the stored procedure , you must alter the stored procedure. How you do that?

7) Type the following

ALTER PROCEDURE uspGetCustomers
AS
SET NOCOUNT ON
select * from SalesLT.Customer where CustomerID=21

Hit the execute button. Then type

exec uspGetCustomers

You will notice that there are no messages created this time.

8) One can delete the stored procedure. The T-SQL to do that is

drop procedure uspGetCustomers

do not hit the execute button unless you want it actually deleted!!!

So far we have created,executed,altered  and deleted a stored procedure.

A simple and short definition of a stored procedure could be:

It is a collection of  one or more T-SQL statements that are compiled together and given a name that allows you to call and reuse those sql statements by simply calling that name.

  1. You can pass information into the stored procedure as an input parameter
  2. You can retrieve scalar values from a stored procedure using output parameters or return values
  3. A stored procedure can return result sets
  4. Stored procedures can be executed from a user query using the SQL Server Management Studio
  5. Stored procedures can be executed from code, like a .NET application , using ADO.Net or LINQ to SQL as data access technologies.
  6. Stored procedures can call other stored procedures

Why we should use stored procedures?What are we gaining from using a stored procedure? We could simply write the T-SQL statements in our .net application, without using a stored procedure.

  • With stored procedures we can implement our  business  rules in a much easier and encapsulated way. When an application wants to access our corporate data through a stored procedure we can be certain that there will be no harm done to our data.
  • Stored procedures allows various applications to use the stored procedure and hence share the logic of our application.Of course you can create components-classes and keep the business rules of your application in those components.
  • Stored procedures help us to secure our application. We can restrict users to update or delete data in our database. Certain privileges can be granted to different users.
  • The possibility of becoming a victim of SQL injection is almost eliminated with stored procedures.
  • We can increase the performance of our application with stored procedures. When we call the the stored procedure many times, the execution plan is reused without having to rebuild the query tree or recompiling the execution plan. The execution plan troubles some people so we better give a short explanation. During the execution of a stored procedure , the query tree is created. A query tree is an internal representation of an SQL statement where the single parts that it is built from are stored separately. The query tree is passed to the SQL Server optimiser that compiles and optimises it.The optimiser scans the query tree and it develops a plan that is the best way to execute the data. This is the execution plan and that is what it is executed.
  • When a stored procedure is called SQL Server will check if the execution plan exists in the cache and use this, if not it will create a new execution plan. If we have many calls to the stored procedure there is no need to create again a query tree or compile the plan again. Simply the existed execution plan is reused.

We have created a very simple stored procedure in our previous example. We will demonstrate how to use a stored procedure with an input parameter which allow us to have more generic-useful stored procedures.

1) Launch SQL Server Management Studio

2) Connect to the Local Instance of your SQL Server

3) Make the AdventureWorksLT database as the current database

4) Open a new query window and type the following simple T-SQL code that follows. Hit the execute button

CREATE PROCEDURE uspGetCustomers
@CustomerID Int
AS
SELECT *
FROM SalesLT.Customer
WHERE CustomerID = @CustomerID

In this example, I allow the application-user to pass to the procedure the customerID and the respective data gets returned for that customerID.

5) If you want to execute this procedure you can type in a query window

exec uspGetCustomers 18

6) You can have a stored procedure with 2 input parameters. Type the following T-SQL in a query window and hit the execute button. In this procedure we try to get the products with  their color and categories specified as input parameters.

CREATE PROCEDURE uspGetByColorAndCategory
@Color NVARCHAR(15),
@ProductCategoryID INT
AS
SELECT ProductID, Name, ProductNumber
FROM SalesLT.Product
WHERE Color = @Color
AND ProductCategoryID = @ProductCategoryID

7) If you want to execute this stored procedure, you can type

exec uspGetByColorAndCategory ‘Black’,’18′

8) Stored procedures can return back multiple result sets. Create a new procedure with the following code.Hit execute

CREATE PROCEDURE uspGetCustomersmultiple
AS
select * from SalesLT.Customer where CustomerID=21
select * from SalesLT.Customer where CustomerID=22

9) Execute the stored procedure by typing

exec uspGetCustomersmultiple

10) We can create a stored procedure using output parameters. In this example I need to retrieve a specific color and size scalar values for a given ProductID from the product table. So you can see the two variables that I declare and will use as output parameters. In this procedure I just pass in my output variables the values of Color and Size for the given ProductID which is the input parameter. So type the following statement in a query window and execute it.

CREATE PROCEDURE uspProducts
@ProductID int,
@Color nvarchar(5) OUTPUT,
@Size nvarchar(5) OUTPUT
AS
SELECT @Color = Color, @Size = Size
FROM SalesLT.Product
WHERE ProductID = @ProductID

11) We must declare two new variables to receive the values of the two output parameters. Then we need to execute the stored procedure by passing it a productID of our choice. Finally we just output in the query window the two values which are the output parameters.Type the following statements and hit execute.

DECLARE @myColor NVARCHAR(5)
DECLARE @mySize NVARCHAR(5)
exec uspProducts 680, @myColor OUT, @mySize OUT
SELECT @myColor + ‘ – ‘ + @mySize AS ProductDetails

That is all for now on this topic. For more info on stored procedures click here

Add to FacebookAdd to NewsvineAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to Ma.gnoliaAdd to TechnoratiAdd to Furl

How to force trigger order in SQL SERVER June 1, 2008

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags: , ,
2 comments

Often when we develop an application and design the database we enforce business logic with the use of triggers.

Sometimes the business logic dictates that we need to define two triggers on a table which must fire in a specific order on the same table action. For example when we insert rows in a table (INSERT statement) two triggers must fire and the second must fire after the first one, in order our logic to be implemented correctly.

Generally speaking multiple triggers on the same table for the same action are not fired in a predefined oreder. We can set the firing order for 2 AFTER triggers.We can do this by using the system stored procedure sp_settriggerorder. Do not try to do the same with INSTEAD OF triggers. You will receive an error.

I have taken the definition of sp_settriggerorder  from Books OnLine.

sp_settriggerorder [ @triggername = ] [ triggerschema. ] triggername
        , [ @order = ] value
        , [ @stmttype = ] statement_type
        [ , [ @namespace = ] { ‘DATABASE’ | ‘SERVER’ | NULL } ] 

A brief explanation of the arguments follows.

[ @triggername = ] [ triggerschema. ] triggername
It is the name of the trigger and the schema to which it belongs.
[ @order = ] value
Is the setting for the new order of the trigger. value is varchar(10) and it can be any one of the following values.
First – Trigger is fired first.
Last – Trigger is fired last.
None – Trigger is fired in undefined order.

[ @stmttype = ] ‘statement_type

Specifies the SQL statement that fires the trigger. statement_type is varchar(50) and can be INSERT, UPDATE, DELETE.

@namespace = { ‘DATABASE’ | ‘SERVER’ | NULL }

Specifies whether a DDL trigger was created on the database or on the server

In order to see an example, let’s create a table and set two INSERT triggers on that table that must fire in a specific order. Let’s call the table employee.

CREATE TABLE [dbo].[Employee](IDENTITY(1,1) NOT NULL

CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED

([EmployeeID] ASC))

Let’s create our triggers

Trigger number 1

create trigger dbo.tr_num_1 on dbo. employee

for insert

as
print ‘i am number 1′

go

 

Trigger number 2

create trigger dbo.tr_num_2 on dbo.employee

for insert

as

print ‘i am number 2′

go

when a new employee is inserted,tr_num_1 must be fired before tr_num_2.
we must insert some values to our table.

insert into employee default values
The triggers fire and we see that they fire in the desired order.
i am number 1
i am number 2
(1 row(s) affected)
Now if we drop the first trigger

DROP TRIGGER [dbo].[tr_num_1]
and create it again

create trigger [dbo].[tr_num_1] on [dbo].[Employee]
for insert
as
print ‘i am number 1′
GO

Now we try to insert a new row in the table.

insert into employee default values

i am number 2
i am number 1
(1 row(s) affected)

Now we have a problem. The second trigger fires first. Let’s fix that by using the sp_settriggerorder system stored procedure

exec sp_settriggerorder @triggername = ‘tr_num_1′,

@order=‘first’,

@stmttype= ‘insert’,
@namespace= null

exec sp_settriggerorder @triggername = ‘tr_num_2′,

@order= ‘last’,

@stmttype= ‘insert’,

@namespace = null

now if we enter a record in our table

insert into employee default values

we will see the following results

i am number 1
i am number 2
(1 row(s) affected)

The firing order is correct!!!!

date and time data types -sql server 2008 March 10, 2008

Posted by fofo in SQL Server 2008.
Tags: ,
add a comment

in this post i am going to introduce the date and time data types that ship with sql server 2008. up to this point sql server had a datetime and smalldatetime data types to support date and time data. developers experienced many problems with those data types. sometimes we did not have enough precision or some other times we did have too much information.for example many times we had to convert the datetime to character string,strip the time component and then convert back to datetime.lets see some examples of the new data type

DECLARE @mybeautifulDate date

SELECT @mybeautifulDate = GETDATE()

PRINT @mybeautifulDate

execute the above statement

execute the following sql statement

DECLARE @Date dateSELECT @Date = ’1977-02-17′

PRINT @Date

with the new date data type we only need 3 bytes of data to store our data since we do not store the time component.

lets have a look at the new time data type. in a new sql query window type and execute the following

DECLARE @MyTime time = GETDATE(),

@MyTime7 time(7) = GETDATE(),

@MyTime6 time(6) = GETDATE(),

@MyTime5  time(5) = GETDATE(),

@MyTime4 time(4) = GETDATE(),

@MyTime3 time(3) = GETDATE(),

@MyTime2 time(2) = GETDATE(),

@MyTime1 time(1) = GETDATE(),

@MyTime0 time(0) = GETDATE()

PRINT @MyTime

PRINT @MyTime7

PRINT @MyTime6

PRINT @MyTime5

PRINT @MyTime4

PRINT @MyTime3

PRINT @MyTime2

PRINT @MyTime1

PRINT @MyTime0

 the results shoule be like these

21:57:08.327000021:57:08.327000021:57:08.32700021:57:08.3270021:57:08.3270

21:57:08.327

21:57:08.33

21:57:08.3

21:57:08

the number inside the parenthesis indicate the precision we want to have for our data.it takes 3-5 bytes in the server to store time data depending on the precision we desire.

there is another date data type, namely datetime2. it provides us with more precision that we had with the existing datetime data types.

if we type this sql statement

DECLARE @datetime2 as DATETIME2(7) = GETDATE()

PRINT @datetime2

the result will be

2008-03-10 22:05:49.4200000we have a more precise datetime datatype.another new date data type is the datetimeoffset that has the same precision as datetime2 but stores the GMT offset as well.

DECLARE @datetimeoffset as DATETIMEOFFSET(7) = ’1977-02-17 14:30:00 -09:00′

PRINT @datetimeoffset

if i execute this sql statement the result will be

1977-02-17 14:30:00.0000000 -09:00

it requires 10 bytes of storage.

hope it helps

new features sql server 2008-user defined table type and table valued parameters March 10, 2008

Posted by fofo in SQL Server 2008.
Tags: ,
9 comments

In Sql Server 2008 you can pass a table variable in a stored procedure as a parameter. now you have the ability to send multiple rows of data in a stored procedure.one main advantage of that is that it will reduce the amount of round trips to the server. 

we are going to walk through the following example that explains the new features in sql server 2008. we can create a sample database. for example

create database sqlserver2008sample

let’s create a table- a customers table

CREATE TABLE [dbo].[Customers](

[Cust_ID] [int] NOT NULL,

[Cust_Name] [varchar](50) NOT NULL,

[Cust_Surname] [varchar](50) NOT NULL,

[Cust_Email] [varchar](50) NOT NULL)

we must insert some values in the table. we can do that by using a stored procedure

create procedure insertintocustomer(@Cust_ID int,@Cust_Name varchar(50),@Cust_Surname varchar(50),@Cust_Email varchar(50))

as

begin

insert into customers

values(

@Cust_ID, @Cust_Name,@Cust_Surname,@Cust_Email)

end

in order to insert values in the table we must execute that stored procedure multiple times. for example

execute insertintocustomer 1,‘robbie’,‘fowler’,‘rb@liverpool.com’

execute insertintocustomer 2,‘michael’,‘owen’,‘mo@liverpool.com’

now lets do the same by inserting table value parameters. 

first we create a user defined table data type.

create type customertype as table

(Cust_ID int,Cust_Name varchar(50),Cust_Surname varchar(50),Cust_Email varchar(50))

then we create the new stored procedure that takes the table type as a parameter.

create procedure newcustomer(@Customer_details customertype READONLY)

as

begin

insert into customers

select * from @Customer_details

end

lets create a variable of table data type

declare @customers customertype

lets fill the table variable by using insert statements

insert into @customers values (1,‘steven’,‘gerrard’,‘sg@liverpool.com’)

insert into @customers values (2,‘jamie’,‘caragher’,‘jc@liverpool.com’)

now we can execute the stored procedure by passing the table value parameter- @customers

execute newcustomer @Customers

if we now try a select statement in the customers table we will see the new values added.

hope it helps. 

Follow

Get every new post delivered to your Inbox.

Join 1,786 other followers