Tuesday, December 28, 2010

Concept of Store Procedure

What is Store Procedure

Stored procedures are precompiled database queries (set of SQL statements) that improve the security, efficiency and usability of database client/server applications. Developers specify a stored procedure in terms of input and output variables. They then compile the code on the database platform and make it available to application developers for use in other environments, such as web applications. All of the major database platforms, including Oracle, SQL Server and MySQL support stored procedures. The major benefits of this technology are the substantial performance gains from precompiled execution, the reduction of client/server traffic, development efficiency gains from code reuse and abstraction and the security controls inherent in granting users permissions on specific stored procedures instead of the underlying database tables.

Benefits of Stored Procedures

• 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: SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = 'FL'
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'

How to execute Store Procedure

Exec Storeprocedurename @parm1,@parm2,@parm3

Points to keep in mind when Store Procedure

Some of the points mentioned in this article is getting through the following site. I added them for my future reference of getting all information at one point.

http://www.devx.com/getHelpOn/10MinuteSolution/20057/1954

Spell out your Procedure’s Purpose

A stored procedure works best when doing only one thing. However, some shops are under pressure to localize the logic underlying data modifications. This centralizes code maintenance by putting the logic in one procedure instead of three or four (e.g., procedures for insert, update, and delete). Unfortunately, combining these actions makes the underlying procedure exponentially more complicated. Instead of doing only one thing, the procedure is pressed into general service, doing three or four things. Your first line of defense in debugging this type of general-purpose stored procedure is a good header that enables you to document its purpose(s). Once the header is set up and maintained, you have a running history of what is happening in the code.

Header contains details like Author, Date, Last Rev, Revision History, Purpose, Creates, uses, parameters in, parameters out , Relations, Good Joins, Parking Lot

Determine the Flow of Control

By branching based on the parameters sent to the procedure, you delegate control to the relevant sections to do the DML required. Assume you have a procedure that has one parameter: @Action, which could be an Insert, Update, or Delete parameter. To test the value of @Action and perform accordingly, you need a structure something like this:

IF @Action = XXXXXX
BEGIN
Your code here
END

You should get this fleshed out by pseudo-coding your intention first and then creating the logic to accomplish what you intend. Since most errors are logic errors, getting this down in stub form will assure that the code is at least executing the flow of control correctly.

Basic things needs to check while Testing Store Procedure

  • Naming convention of Store procedure
  • Whether SP is part of DB or not
  • Name of parameter, No of parameters, type of parameters
  • Check for the Outputs
    • When output is zero (zero row affected)
    • When some records are extracted
    • Output contains many records
    • What a stored procedure is supposed to do and not supposed to do
    • Write a simple queries to see if a stored procedure populates right data
  • Check for the input parameters
  • Check for the valid data for each parameter
  • Check for the invalid data for each parameter
  • Check for the boundary value of data
  • Check for all the required parameters
  • Check parameter for data type
  • Check for the Return values
  • Check if SP return any values
  • Check if failure occurs then it should return nonzero
  • Check for the Error Messages
    • Make stored procedure fail and cause every error message to occur at least once
    • Find out any exception that doesn’t have a predefined error message
  • Check for other values
  • Whether a stored procedure grants correct access privilege to a group/user
  • See if a stored procedure hits any trigger error, index error, and rule error
  • Look into a procedure code and make sure major branches are test covered.
  • Integration check for the procedure
  • Group related stored procedures together. Call them in particular order
  • If there are many sequences to call a group of procedures, find out equivalent classes and run tests to cover every class.
  • Make invalid calling sequence and run a group of stored procedures.
  • Design several test sequences in which end users are likely to do business and do stress tests

Let the System Do the Work with everything

By creating a parameter mill, you can feed a stored procedure a collection of parameters that represent the universe of things it can accept, enabling you to test the scope of conditions anticipated by your code. You can automate the boundary conditions with a table and procedure to do the drudgery for you.

CREATE PROCEDURE dbo.usp_TestFacilityRoster
@InAction VARCHAR(15),
@EntryMonth INT,
@EntryYear I
@FileType CHAR(1)
AS
IF @InAction ='NEWREINS' or @InAction = 'NEWREINSSNP'
BEGIN
--do stuff for new members
END
IF @InAction = 'DISENROL' OR @InAction = 'EXISTING'
BEGIN
--do common stuff for existing or disenrolled members
IF @InAction = 'DISENROL'
BEGIN
--specific for disenroll
ELSE
IF @InAction = 'EXISTING'
BEGIN
--specific for existing
END

Create a test plan table with the following code

create table p1 (param1 char(8))
create table p2 (param2 int)
create table p3 (param3 int)
create table p4 (param4 char(1))
create table testplan (param1 char(8), param2 int, param3 int, param4 char(1))
insert p1 values('NEWREINS')
insert p1 values('DISENROL')
insert p1 values('EXISTING')
insert p2 values(1)
insert p2 values(2)
insert p2 values(3)
insert p2 values(4)
insert p2 values(5)
insert p2 values(6)
insert p2 values(7)
insert p2 values(8)
insert p2 values(9)
insert p2 values(10)
insert p2 values(11)
insert p2 values(12)
insert p3 values(1999)
insert p3 values(2000)
insert p3 values(2001)
insert p3 values(2002)
insert p3 values(2003)
insert p3 values(2004)
insert p4 values('A')
insert p4 values('S')
insert p4 values('P')
insert testplan select * from p1,p2,p3,p4

This will create a Cartesian Product (cross join) of all the combinations of the parameters your procedure can accept.

During development, you try to mimic the call to your procedure by writing something like this near the top of the code:

/* TEST BLOCK
set @InAction ='NEWREINS'
set @entrymonth = 7
set @entryyear = 2003
*/

Instead, you can create a string to execute the stored procedure that leaves placeholders for your procedure's parameters:

select 'Execute dbo.usp_TestFacilityRoster('+
''''+
param1 + ''''+','
+cast(param2 as char(2))
+','+
+cast(param3 as char(4))
+','
+''''+param4+''''
+')'
from testplan

This code doesn't execute it though. You need to use dynamic SQL to execute the string. So the above code can be wrapped in a cursor like this:

set nocount on
declare @param1 varchar(25)
,@param2 varchar(2)
,@param3 varchar(4)
,@param4 varchar(25)
declare C1 Cursor for
select param1, param2, param3, param4
from testplan (nolock)
open C1
fetch next from C1
into @param1, @param2, @param3, @param4
while @@fetch_status = 0
begin
exec dbo.usp_TestFacilityRoster, @param1, @param2, @param3,
@param4
fetch next from C1
into @param1, @param2, @param3, @param4
end
close C1
deallocate C1

-- this statement builds the string from the table
Update Testplan
Set @sqlstring2 = @sqlstring2 + cast(param1 as char(8)) + ', '
+cast(param2 as char(2)) + ', ' + cast(param3 as char(4))+ ', '
+cast(param4 as char(1))
print @sqlstring2
set @sqlstring = @sqlstring + @sqlstring2
execute sp_executesql @sqlstring

You also can use this approach from the command line and imitate the testing procedure outlined above by using the FOR IN DO construct in a batch file, or in better flow of control language from WSH so that you can make repeated calls to your procedure with the different parameter sets. However, the approach given here lets you work from within Query Analyzer without having to resort to the command window or another language to get the job done.

These ideas were necessary for me to circumvent the absence of permissions to run the SQL Debugger or to install my own error messages so that I could use 'raise error' in my assignment. This Solution has provided a few ideas for getting around similar limitations in your own environment, and they lets you test your code automatically to have a higher degree of confidence in the correctness of your own solutions

Thursday, December 9, 2010

Concept of JOIN

Concept of JOIN:

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

INNER JOIN

This join returns rows when there is at least one match in both the tables.
Example:
SELECT * FROM Table1 T1 INNER JOIN Table2 T2 ON T1.col1=T2.col1

OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN

This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
Example:

SELECT * FROM Table1 T1 LEFT OUTER JOIN Table2 T2 ON T1.col1=T2.col1

RIGHT OUTER JOIN

This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
Example:
SELECT * FROM Table1 T1 RIGHT OUTER JOIN Table2 T2 ON T1.col1=T2.col1

FULL OUTER JOIN

This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.
Example:
SELECT * FROM Table1 T1 FULL OUTER JOIN Table2 T2 ON T1.col1=T2.col1

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

Below is good reference link for concept of JOIN.
http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/