http://databases.about.com/od/sqlserver/a/procs_vs_functs.htm
SQL Server user-defined functions and stored procedures offer similar functionality. Both allow you to create bundles of SQL statements that are stored on the server for future use. This offers you a tremendous efficiency benefit, as you can save programming time by:
Reusing code from one program to another, cutting down on program development time
Hiding the SQL details, allowing database developers to worry about SQL and application developers to deal only in higher-level languages
Centralize maintenance, allowing you to make business logic changes in a single place that automatically affect all dependent applications
At first glance, functions and stored procedures seem identical. However, there are several subtle, yet important differences between the two:
Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.
Stored procedure allow you to enhance application security by granting users and applications permission to use stored procedures, rather than permission to access the underlying tables. Stored procedures provide the ability to restrict user actions at a much more granular level than standard SQL Server permissions. For example, if you have an inventory table that cashiers must update each time an item is sold (to decrement the inventory for that item by 1 unit), you can grant cashiers permission to use a decrement_item stored procedure, rather than allowing them to make arbitrary changes to the inventory table.
Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.
Overall, stored procedures are one of the greatest treasures available to SQL Server developers. I use them heavily in my databases and encourage you to do the same. The efficiency and security benefits you’ll reap are well worth the upfront investment in time.
Friday, February 26
Stored Procedures in SQL Server
http://databases.about.com/od/sqlserver/a/storedprocedure.htm
Microsoft SQL Server provides the stored procedure mechanism to simplify the database development process by grouping Transact-SQL statements into manageable blocks.
Benefits of Stored Procedures
Why should you use stored procedures? Let's take a look at the key benefits of this technology:
Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.
Stored procedures are very similar to user-defined functions, but there are subtle differences. For more information, read Comparing Stored Procedures and User-Defined Functions.
Structure
Stored procedures are extremely similar to the constructs seen in other programming languages. They accept data in the form of input parameters that are specified at execution time. These input parameters (if implemented) are utilized in the execution of a series of statements that produce some result. This result is returned to the calling environment through the use of a recordset, output parameters and a return code. That may sound like a mouthful, but you'll find that stored procedures are actually quite simple.
Example
Let's take a look at a practical example. Assume we have the table shown at the bottom of this page, named Inventory. This information is updated in real-time and warehouse managers are constantly checking the levels of products stored at their warehouse and available for shipment. In the past, each manager would run queries similar to the following:
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = 'FL'
This resulted in very inefficient performance at the SQL Server. Each time a warehouse manager executed the query, the database server was forced to recompile the query and execute it from scratch. It also required the warehouse manager to have knowledge of SQL and appropriate permissions to access the table information.
We can simplify this process through the use of a stored procedure. Let's create a procedure called sp_GetInventory that retrieves the inventory levels for a given warehouse. Here's the SQL code:
CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location
Our Florida warehouse manager can then access inventory levels by issuing the command
EXECUTE sp_GetInventory 'FL'
The New York warehouse manager can use the same stored procedure to access that area's inventory.
EXECUTE sp_GetInventory 'NY'
Granted, this is a simple example, but the benefits of abstraction can be seen here. The warehouse manager does not need to understand SQL or the inner workings of the procedure. From a performance perspective, the stored procedure will work wonders. The SQL Sever creates an execution plan once and then reutilizes it by plugging in the appropriate parameters at execution time.
Now that you've learned the benefits of stored procedures, get out there and use them! Try a few examples and measure the performance enhancements achieved -- you'll be amazed!
Inventory Table
ID Product Warehouse Quantity
142 Green beans NY 100
214 Peas FL 200
825 Corn NY 140
512 Lima beans NY 180
491 Tomatoes FL 80
379 Watermelon FL 85
Microsoft SQL Server provides the stored procedure mechanism to simplify the database development process by grouping Transact-SQL statements into manageable blocks.
Benefits of Stored Procedures
Why should you use stored procedures? Let's take a look at the key benefits of this technology:
Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.
Stored procedures are very similar to user-defined functions, but there are subtle differences. For more information, read Comparing Stored Procedures and User-Defined Functions.
Structure
Stored procedures are extremely similar to the constructs seen in other programming languages. They accept data in the form of input parameters that are specified at execution time. These input parameters (if implemented) are utilized in the execution of a series of statements that produce some result. This result is returned to the calling environment through the use of a recordset, output parameters and a return code. That may sound like a mouthful, but you'll find that stored procedures are actually quite simple.
Example
Let's take a look at a practical example. Assume we have the table shown at the bottom of this page, named Inventory. This information is updated in real-time and warehouse managers are constantly checking the levels of products stored at their warehouse and available for shipment. In the past, each manager would run queries similar to the following:
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = 'FL'
This resulted in very inefficient performance at the SQL Server. Each time a warehouse manager executed the query, the database server was forced to recompile the query and execute it from scratch. It also required the warehouse manager to have knowledge of SQL and appropriate permissions to access the table information.
We can simplify this process through the use of a stored procedure. Let's create a procedure called sp_GetInventory that retrieves the inventory levels for a given warehouse. Here's the SQL code:
CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location
Our Florida warehouse manager can then access inventory levels by issuing the command
EXECUTE sp_GetInventory 'FL'
The New York warehouse manager can use the same stored procedure to access that area's inventory.
EXECUTE sp_GetInventory 'NY'
Granted, this is a simple example, but the benefits of abstraction can be seen here. The warehouse manager does not need to understand SQL or the inner workings of the procedure. From a performance perspective, the stored procedure will work wonders. The SQL Sever creates an execution plan once and then reutilizes it by plugging in the appropriate parameters at execution time.
Now that you've learned the benefits of stored procedures, get out there and use them! Try a few examples and measure the performance enhancements achieved -- you'll be amazed!
Inventory Table
ID Product Warehouse Quantity
142 Green beans NY 100
214 Peas FL 200
825 Corn NY 140
512 Lima beans NY 180
491 Tomatoes FL 80
379 Watermelon FL 85
Thursday, February 25
heighest salary in any position...
SELECT netAmt FROM jppPurchaseOrder AS P
WHERE 1 = (SELECT COUNT(*) FROM jppPurchaseOrder WHERE netAmt>p.netAmt)
FOR '0' its Top1
FOR '1' its Top2
FOR '2' its Top3
etc...
for 2nd heighest
SELECT Max(netAmt) FROM jppPurchaseOrder
WHERE netAmt < (SELECT Max(netAmt) FROM jppPurchaseOrder)
WHERE 1 = (SELECT COUNT(*) FROM jppPurchaseOrder WHERE netAmt>p.netAmt)
FOR '0' its Top1
FOR '1' its Top2
FOR '2' its Top3
etc...
for 2nd heighest
SELECT Max(netAmt) FROM jppPurchaseOrder
WHERE netAmt < (SELECT Max(netAmt) FROM jppPurchaseOrder)
Wednesday, February 24
Database Mail in SQL Server 2005
http://www.databasejournal.com/features/mssql/article.php/3626056/Database-Mail-in-SQL-Server-2005.htm
The SQL Mail problems, that we faced in SQL Server 7.0 and 2000, are no more. SQL Server 2005 supports and uses SMTP email now and there is no longer a need to MAPI client to send email. In SQL Server 2005, the mail feature is called Database Mail. In this article, I am going to demonstrate step-by-step, with illustrations, how to configure Database Mail and send email from SQL Server.
Database Mail has four components.
1. Configuration Component
Configuration component has two sub components. One is the Database Mail account, which contains information such as the SMTP server login, Email account, Login and password for SMTP mail.
The Second sub component is Database Mail Profile. Mail profile can be Public, meaning members of DatabaseMailUserRole in MSDB database can send email. For private profile, a set of users should be defined.
2. Messaging Component
Messaging component is basically all of the objects related to sending email stored in the MSDB database.
3. Database Mail Executable
Database Mail uses the DatabaseMail90.exe executable to send email.
4. Logging and Auditing component
Database Mail stores the log information on MSDB database and it can be queried using sysmail_event_log.
Step 1
Before setting up the Database Mail profile and accounts, we have to enable the Database Mail feature on the server. This can be done in two ways. The first method is to use Transact SQL to enable Database Mail. The second method is to use a GUI.
In the SQL Server Management Studio, execute the following statement.
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure
go
Alternatively, you could use the SQL Server Surface area configuration. Refer Fig 1.0.
Fig 1.0
Step 2
The Configuration Component Database account can be enabled by using the sysmail_add_account procedure. In this article, we are going create the account, "MyMailAccount," using mail.optonline.net as the mail server and
makclaire@optimumonline.net as the e-mail account.
Please execute the statement below.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MyMailAccount',
@description = 'Mail account for Database Mail',
@email_address = 'makclaire@optonline.net',
@display_name = 'MyAccount',
@username='makclaire@optonline.net',
@password='abc123',
@mailserver_name = 'mail.optonline.net'
Step 3
The second sub component of the configuration requires us to create a Mail profile.
In this article, we are going to create "MyMailProfile" using the sysmail_add_profile procedure to create a Database Mail profile.
Please execute the statement below.
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MyMailProfile',
@description = 'Profile used for database mail'
Step 4
Now execute the sysmail_add_profileaccount procedure, to add the Database Mail account we created in step 2, to the Database Mail profile you created in step 3.
Please execute the statement below.
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MyMailProfile',
@account_name = 'MyMailAccount',
@sequence_number = 1
Step 5
Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.
Please execute the statement below.
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MyMailProfile',
@principal_name = 'public',
@is_default = 1 ;
Step 6
Now let us send a test email from SQL Server.
Please execute the statement below.
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='mak_999@yahoo.com',
@subject = 'My Mail Test',
@body = @body1,
@body_format = 'HTML' ;
You will get the message shown in Fig 1.1.
Fig 1.1
Moreover, in a few moments you will receive the email message shown in Fig 1.2.
Fig 1.2
You may get the error message below, if you haven't run the SQL statements from step 1.
Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of
component 'Database Mail XPs' because this component is turned off as part of
the security configuration for this server. A system administrator can enable
the use of 'Database Mail XPs' by using sp_configure. For more information
about enabling 'Database Mail XPs', see "Surface Area Configuration"
in SQL Server Books Online.
You may see this in the database mail log if port 25 is blocked. Refer Fig 1.3.
Fig 1.3
Please make sure port 25 is not blocked by a firewall or anti virus software etc. Refer Fig 1.4.
Fig 1.4
Step 7
You can check the configuration of the Database Mail profile and account using SQL Server Management Studio by right clicking Database Mail [Refer Fig 1.5] and clicking the Configuration. [Refer Fig 1.6]
Fig 1.5
Fig 1.6
Step 8
The log related to Database Mail can be viewed by executing the statement below. Refer Fig 1.7.
SELECT * FROM msdb.dbo.sysmail_event_log
Fig 1.7
Conclusion
This article has demonstrated step-by-step instructions, with illustrations, how to configure Database Mail and send email from SQL Server.
The SQL Mail problems, that we faced in SQL Server 7.0 and 2000, are no more. SQL Server 2005 supports and uses SMTP email now and there is no longer a need to MAPI client to send email. In SQL Server 2005, the mail feature is called Database Mail. In this article, I am going to demonstrate step-by-step, with illustrations, how to configure Database Mail and send email from SQL Server.
Database Mail has four components.
1. Configuration Component
Configuration component has two sub components. One is the Database Mail account, which contains information such as the SMTP server login, Email account, Login and password for SMTP mail.
The Second sub component is Database Mail Profile. Mail profile can be Public, meaning members of DatabaseMailUserRole in MSDB database can send email. For private profile, a set of users should be defined.
2. Messaging Component
Messaging component is basically all of the objects related to sending email stored in the MSDB database.
3. Database Mail Executable
Database Mail uses the DatabaseMail90.exe executable to send email.
4. Logging and Auditing component
Database Mail stores the log information on MSDB database and it can be queried using sysmail_event_log.
Step 1
Before setting up the Database Mail profile and accounts, we have to enable the Database Mail feature on the server. This can be done in two ways. The first method is to use Transact SQL to enable Database Mail. The second method is to use a GUI.
In the SQL Server Management Studio, execute the following statement.
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure
go
Alternatively, you could use the SQL Server Surface area configuration. Refer Fig 1.0.
Fig 1.0
Step 2
The Configuration Component Database account can be enabled by using the sysmail_add_account procedure. In this article, we are going create the account, "MyMailAccount," using mail.optonline.net as the mail server and
makclaire@optimumonline.net as the e-mail account.
Please execute the statement below.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MyMailAccount',
@description = 'Mail account for Database Mail',
@email_address = 'makclaire@optonline.net',
@display_name = 'MyAccount',
@username='makclaire@optonline.net',
@password='abc123',
@mailserver_name = 'mail.optonline.net'
Step 3
The second sub component of the configuration requires us to create a Mail profile.
In this article, we are going to create "MyMailProfile" using the sysmail_add_profile procedure to create a Database Mail profile.
Please execute the statement below.
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MyMailProfile',
@description = 'Profile used for database mail'
Step 4
Now execute the sysmail_add_profileaccount procedure, to add the Database Mail account we created in step 2, to the Database Mail profile you created in step 3.
Please execute the statement below.
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MyMailProfile',
@account_name = 'MyMailAccount',
@sequence_number = 1
Step 5
Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.
Please execute the statement below.
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MyMailProfile',
@principal_name = 'public',
@is_default = 1 ;
Step 6
Now let us send a test email from SQL Server.
Please execute the statement below.
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='mak_999@yahoo.com',
@subject = 'My Mail Test',
@body = @body1,
@body_format = 'HTML' ;
You will get the message shown in Fig 1.1.
Fig 1.1
Moreover, in a few moments you will receive the email message shown in Fig 1.2.
Fig 1.2
You may get the error message below, if you haven't run the SQL statements from step 1.
Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of
component 'Database Mail XPs' because this component is turned off as part of
the security configuration for this server. A system administrator can enable
the use of 'Database Mail XPs' by using sp_configure. For more information
about enabling 'Database Mail XPs', see "Surface Area Configuration"
in SQL Server Books Online.
You may see this in the database mail log if port 25 is blocked. Refer Fig 1.3.
Fig 1.3
Please make sure port 25 is not blocked by a firewall or anti virus software etc. Refer Fig 1.4.
Fig 1.4
Step 7
You can check the configuration of the Database Mail profile and account using SQL Server Management Studio by right clicking Database Mail [Refer Fig 1.5] and clicking the Configuration. [Refer Fig 1.6]
Fig 1.5
Fig 1.6
Step 8
The log related to Database Mail can be viewed by executing the statement below. Refer Fig 1.7.
SELECT * FROM msdb.dbo.sysmail_event_log
Fig 1.7
Conclusion
This article has demonstrated step-by-step instructions, with illustrations, how to configure Database Mail and send email from SQL Server.
Trigger to send mail Alert
http://blog.netnerds.net/2008/02/create-a-basic-sql-server-2005-trigger-to-send-e-mail-alerts/
For as many times as I have read about sending e-mails using SQL Server triggers, I've rarely come across actual code samples. After someone asked for a "Triggers for Dummies" example in a Facebook SQL group, I created the following example which uses a trigger to alert a manager that an expensive item has been entered into inventory.
First, if SQL Mail isn't enabled and a profile hasn't been created, we must do so.
--// First, enable SQL SMail
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go
--//Now create the mail profile. CHANGE @email_address,@display_name and @mailserver_name VALUES to support your environment
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBMailAccount',
@email_address = 'sqlserver@domain.com',
@display_name = 'SQL Server Mailer',
@mailserver_name = 'exchangeServer'
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBMailProfile'
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBMailProfile',
@account_name = 'DBMailAccount',
@sequence_number = 1 ;
Now that SQL will support sending e-mails, let's create the sample table. This is not a useful or well designed table by any means -- it's just a simple example table:
CREATE TABLE dbo.inventory (
item varchar(50),
price money
)
GO
Now that SQL mail and the table are setup, we will create a trigger that does the following:
Creates an AFTER INSERT trigger named expensiveInventoryMailer on the inventory table. This means that the trigger will be executed after the data has been entered.
Checks for items being entered that have a price of $1000 or more
If there is a match, an email is sent using the SQL Mail profile we used above.
CREATE TRIGGER expensiveInventoryMailer ON dbo.inventory AFTER INSERT AS
DECLARE @price money
DECLARE @item varchar(50)
SET @price = (SELECT price FROM inserted)
SET @item = (SELECT item FROM inserted)
IF @price >= 1000
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'Expensive item "' + @item + '" entered into inventory at $' + CAST(@price as varchar(10)) + '.'
--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients=N'manager@domain.com', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'
END
GO
The only way to test a trigger is to add actual data, so let's do that here:
insert into inventory (item,price) values ('Vase',100)
insert into inventory (item,price) values ('Oven',1000)
Your email should arrive very quickly. If it doesn't, check the SQL Server mail log in SQL Management Studio by running SELECT * FROM sysmail_allitems.
Have fun!
For as many times as I have read about sending e-mails using SQL Server triggers, I've rarely come across actual code samples. After someone asked for a "Triggers for Dummies" example in a Facebook SQL group, I created the following example which uses a trigger to alert a manager that an expensive item has been entered into inventory.
First, if SQL Mail isn't enabled and a profile hasn't been created, we must do so.
--// First, enable SQL SMail
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go
--//Now create the mail profile. CHANGE @email_address,@display_name and @mailserver_name VALUES to support your environment
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBMailAccount',
@email_address = 'sqlserver@domain.com',
@display_name = 'SQL Server Mailer',
@mailserver_name = 'exchangeServer'
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBMailProfile'
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBMailProfile',
@account_name = 'DBMailAccount',
@sequence_number = 1 ;
Now that SQL will support sending e-mails, let's create the sample table. This is not a useful or well designed table by any means -- it's just a simple example table:
CREATE TABLE dbo.inventory (
item varchar(50),
price money
)
GO
Now that SQL mail and the table are setup, we will create a trigger that does the following:
Creates an AFTER INSERT trigger named expensiveInventoryMailer on the inventory table. This means that the trigger will be executed after the data has been entered.
Checks for items being entered that have a price of $1000 or more
If there is a match, an email is sent using the SQL Mail profile we used above.
CREATE TRIGGER expensiveInventoryMailer ON dbo.inventory AFTER INSERT AS
DECLARE @price money
DECLARE @item varchar(50)
SET @price = (SELECT price FROM inserted)
SET @item = (SELECT item FROM inserted)
IF @price >= 1000
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'Expensive item "' + @item + '" entered into inventory at $' + CAST(@price as varchar(10)) + '.'
--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients=N'manager@domain.com', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'
END
GO
The only way to test a trigger is to add actual data, so let's do that here:
insert into inventory (item,price) values ('Vase',100)
insert into inventory (item,price) values ('Oven',1000)
Your email should arrive very quickly. If it doesn't, check the SQL Server mail log in SQL Management Studio by running SELECT * FROM sysmail_allitems.
Have fun!
Subscribe to:
Posts (Atom)