CLR Stored Procedure

SQL SERVER – Introduction to CLR – Simple Example of CLR Stored Procedure

CLR is abbreviation of Common Language Runtime. In SQL Server 2005 and later version of it database objects can be created which are created in CLR. Stored Procedures, Functions, Triggers can be coded in CLR. CLR is faster than T-SQL in many cases. CLR is mainly used to accomplish task which are not possible by T-SQL or can use lots of resources. CLR can be usually implemented where there is intense string operation, thread management or iteration methods which can be complicated for T-SQL. Implementing CLR provides more security to Extended Stored Procedure.

Let us create one very simple CLR where we will print current system datetime.

1) Open Microsoft Visual Studio >> Click New Project >> Select Visual C# >> Database >> SQL Server Project

2) Either choose from existing database connection as reference or click on Add New Reference. In my example I have selected Add New Reference.

3) If you have selected existing reference skip to next step or add database reference as displayed in image.

4) Once database reference is added following project will be displayed in Solution Explorer. Right click on Solution Explorer >> Click on Add >> Stored Procedure.

5) Add new stored procedure template from following screen.

6) Once template added it will look like following image.

7) Now where it suggest to //Put your code here. Replace it with code displayed in the image. Once the code is complete do following two steps.
a) Click on menu bar >> Build >> Build ProjectName
b) Click on menu bar >> Build >> Deploy ProjectName
Building and Deploying project should give successful message.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CLRSPTest()
{
SqlPipe sp;
sp = SqlContext.Pipe;
String strCurrentTime = “Current System DateTime is: “
+ System.DateTime.Now.ToString();
sp.Send(strCurrentTime);
}
};
8) Now open SQL Server Management Studio and run following script in Query Editor. It should return current system datetime. Running it again the time will change.

USE AdventureWorks
GOEXEC dbo.CLRSPTest
GO

Read more...

SQL - Join

SQL JOIN joins together two tables on a matching table column, ultimately forming one single temporary table. The key word here is temporary. The tables themselves remain intact, and running a JOIN query does not in any way change the data or table structure. JOIN is another way to select specific data from two or more relational tables.

In order to perform a JOIN query, we need a few pieces of information: the name of the table and table column we want to join on and a condition to meet for the JOIN to happen. This should sound a little confusing as there is much going on in a JOIN query, so let's take a look at an example:

SQL Join Query Code:

USE mydatabase;

SELECT *
FROM orders
JOIN inventory
ON orders.product = inventory.product;

SQL Join Results:

id	customer	day_of_order	product	quantity	id	product	quantity	price
1	Tizag	2008-08-01 00:00:00.000	Hanging Files	11	5	Hanging Files	33	14.99
2	Tizag	2008-08-01 00:00:00.000	Stapler	3	4	Stapler	3	7.99
3	A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	14	5	Hanging Files	33	14.99
4	Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	5	1	19" LCD Screen	25	179.99
5	Tizag	2008-07-25 00:00:00.000	19" LCD Screen	5	1	19" LCD Screen	25	179.99
6	Tizag	2008-07-25 00:00:00.000	HP Printer	4	2	HP Printer	9	89.99

The line beginning with JOIN (Line 4) is where we tell SQL which table we would like to join. The next line (Line 5) is a different story. Here is where we have specified the condition to JOIN ON. In this case, both tables have identical product columns which makes them an ideal target for a join. Basically we are temporarily merging the tables connecting them where they match, the product column.

This type of join matches values from one table column with a corresponding value in another table and uses that match to merge the tables together. In our make-believe store world, this let's us join the inventory table with the orders table to show us all the items we currently have in stock for our customers and also the price of each item.

Let's rework this query a bit and strip away a few of the table columns to make our results easier to read and understand. We will replace the (*) parameter with a list containing only the table columns we are interested in viewing.

SQL Join:

USE mydatabase;

SELECT orders.customer,
orders.day_of_order,
orders.product,
orders.quantity as number_ordered,
inventory.quantity as number_instock,
inventory.price
FROM orders
JOIN inventory
ON orders.product = inventory.product

SQL Results:

customer	day_of_order	product	number_ordered	number_instock	price
Tizag	2008-08-01 00:00:00.000	Hanging Files	11	33	14.99
Tizag	2008-08-01 00:00:00.000	Stapler	3	3	7.99
A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	14	33	14.99
Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	5	25	179.99
Tizag	2008-07-25 00:00:00.000	19" LCD Screen	5	25	179.99
Tizag	2008-07-25 00:00:00.000	HP Printer	4	9	89.99

Since we have one column in each table named the same thing (quantity), we used AS to modify how these columns would be named when our results were returned. These results should be more satisfying and easier to read now that we have removed some of the unnecessary columns.

SQL - Right Join

RIGHT JOIN is another method of JOIN we can use to join together tables, but its behavior is slightly different. We still need to join the tables together based on a conditional statement. The difference is that instead of returning ONLY rows where a join occurs, SQL will list EVERY row that exists on the right side, (The JOINED table).

SQL - Right Join:

USE mydatabase;

SELECT *
FROM orders
RIGHT JOIN inventory
ON orders.product = inventory.product

SQL Results:

id	customer	day_of_order	product	quantity	id	product	quantity	price
4	Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	5	1	19" LCD Screen	25	179.99
5	Tizag	2008-07-25 00:00:00.000	19" LCD Screen	5	1	19" LCD Screen	25	179.99
6	Tizag	2008-07-25 00:00:00.000	HP Printer	4	2	HP Printer	9	89.99
NULL	NULL	NULL	NULL	NULL	3	Pen	78	0.99
2	Tizag	2008-08-01 00:00:00.000	Stapler	3	4	Stapler	3	7.99
1	Tizag	2008-08-01 00:00:00.000	Hanging Files	11	5	Hanging Files	33	14.99
3	A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	14	5	Hanging Files	33	14.99
NULL	NULL	NULL	NULL	NULL	6	Laptop	16	499.99

You should see a new row at the bottom of the results box with a bunch of NULL values. This is a result of the RIGHT JOIN and is the intended result from running the query. We end up with an extra row because inside of the inventory table, the Laptop item was not joined with a product from the orders table. This just means that we have not sold a laptop as of yet and it shouldn't be much a surprise since we already know from querying the orders table in previous lessons that there have been no laptop orders so far.

By specifying RIGHT JOIN, we have told SQL to join together the tables even if no matches are found in the conditional statement. All records that exist in the table on the right side of the conditional statement (ON orders.product = inventory.product) will be returned and NULL values will be placed on the left if no matches are found.

SQL - Left Join

SQL LEFT JOIN works exactly the same way as RIGHT JOIN except that they are opposites. NULL values will appear on the right instead of the left and all rows from the table on the left hand side of the conditional will be returned.

Unfortunately, we will not be able to show a very intuitive example of a LEFT JOIN because of how our tables are structured. The orders table should always have a matching inventory item and if not, that means we are in big trouble as we could be selling items we do not carry in inventory. For good measure, here's what a LEFT JOIN would look like:

SQL Left Join:

USE mydatabase;

SELECT *
FROM orders
LEFT JOIN inventory
ON orders.product = inventory.product

SQL JOIN is intended to bring together data from two tables to form a single larger table, and often, it will paint a more detailed picture of what the data represents. By merging these two data sets, we were able to peer into our database and ensure that each item ordered so far is in stock and ready to be shipped to our customers.

Read more...

Basics of Stored Procedures

SQL Server implements Client/Server technology.A number of client send queries to the Central Server.The server after receiving the query request,parses it for syntax errors and process the request.Since the query passes from the client to the server through the network it adds to network traffic.Hence an increase in the number of queries from the clients leads to network congestion and load on the server.A stored procedure can solve this problem.

It is a collection or batch of T-SQL statements and control-of-flow language that is stored under one name and executed as a single unit.It helps in improving the performance of the query.A stored procedure is a precompiled object and is readily available for the various applications to execute.

Features of stored procedures are:-

1. Improved Performance
2. Reduction in network congestion
3. Enhanced accuracy
4. Better consistency
5. Better security mechanism

Types of procedures

1. User-Defined stored procedures :- They are created and stored in the current database.
2. System stored procedures :- They have names prefixed with sp_. These primarily support various administrative tasks that help manage the SQL SERVER.System stored procedures are stored in the system database and are accessible to the users of all the database.
3. Temporary stored procedures:- It has names prefixed with the #symbol .They are stored in tempdb database and are automatically dropped when the connection terminates.
4. Remote stored procedures :- They are the procedures that are created and stored in databases on remote servers. These can be accessed from various servers,provided user has appropriate permissions.
5. Extended stored procedures:- These are dlls that are executed outside the SQL Server environment.

Syntax to create procedure is 

Code:

CREATE PROCEDURE proc_name

AS

BEGIN

sql_statement1

sql_statement2

END

Where  proc_name specifies the name of the stored procedure.

Syntax to execute procedure is 

Code:

EXECUTE proc_name

Syntax to drop procedure is 

Code:

DROP PROCEDURE proc_name

Read more...