Saturday, 30 July 2011

Stored procedures

Stored procedures are essentially functions that you can create in the database and reuse. What's neat about them from a usability standpoint is that they can take input parameters and then return a result.

Stored procedures have many benefits, including:
  • Speed - Stored procedures are pre-compiled, so the execution plan doesn't have to be figured out each time they're called. This can result in a significant performance improvement.
  • Code reuse and abstraction - Stored procedures often involve complex code. It's nice that this code doesn't have to be rewritten over and over again and that even entry-level SQL programmers can make use of it with a simple stored procedure call. 
  • Security - Permissions can be granted for stored procedures while being restricted for the underlying tables. This allows the DBA to provide a method for SQL programmers and report writers to access and/or manipulate data in a safe way. 
  • Reduced traffic between client and server - Since the bulk of the query is already stored on the server and only the relatively short stored procedure call has to get sent, traffic to the server is decreased.
 CREATE PROCEDURE procedure_name
                @param data_type = default_value,
                @param data_type = default_value,
                @param data_type = default_value
                 AS
                -- statements for procedure here

Code Sample: StoredProcedures/Demos/StoredProc.sql

CREATE PROCEDURE SalesByCategory
 @CategoryName nvarchar(15),
 @OrdYear int = 1998
AS

SELECT ProductName, SUM(OD.Quantity * (1-OD.Discount) * OD.UnitPrice) AS TotalPurchase
FROM "Order Details" od, Orders o, Products p, Categories c
WHERE od.OrderID = o.OrderID
 AND od.ProductID = p.ProductID
 AND p.CategoryID = c.CategoryID
 AND c.CategoryName = @CategoryName
 AND DATEPART(year,OrderDate) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName

--Call stored procedure
EXEC SalesByCategory 'Seafood', 1997
 
Must Read

Recursion is one of the most elegant programming constructsin my opinion. I have used it in dozens of situations and in severalprogramming languages. The basic concept of recursion is straightforward: agiven chunk of code calls itself until some boundary condition is reached. I'lldemonstrate you how to use recursion in T-SQL, using one of the classicexamples of recursion: the factorial calculation.

A factorial is the multiple of any number by all the lessernumbers down to two. For example, factorial(10) isequal to 10 * 9 * 8 * 7 * 6 * 5 * 4 * 3 * 2. (You could add * 1, but whybother?)

The following script implements the factorial algorithm:

CREATE PROCEDURE [dbo].[Factorial_ap]
(
@Number Integer,
@RetVal Integer OUTPUT
)
AS
DECLARE @In Integer
DECLARE @Out Integer
IF @Number != 1
BEGIN
SELECT @In = @Number – 1
EXEC Factorial_ap @In, @Out OUTPUT
SELECT @RetVal = @Number * @Out
END
ELSE
BEGIN
SELECT @RetVal = 1
END
RETURN
GO

Assuming that you want to calculate factorial(n), the procedure will call itself (n-2) times. SQL Server permits recursionto a depth of 32 calls, but at 13, you'll suffer arithmetic overflow. If youexpect to be calculating factorials for large numbers, then you should declarethe variable as BigInt rather than Integer. This willallow you to calculate factorial(20), whichincidentally is 2,432,902,008,176,640,000. The results increase in size soquickly that factorial(21) breaks this implementationas well.

As pretty as the factorial algorithm is, it's unlikely thatyou'll have much use for it in your everyday programming. Nevertheless, itconcisely illustrates the principle of recursion.

Thursday, 28 July 2011

Sorting

The ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by a specified column.
The ORDER BY keyword sort the records in ascending order by default.
If you want to sort the records in a descending order, you can use the DESC keyword.

SQL ORDER BY Syntax

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC


Order by Two Columns

It is also possible to order by more than one column. When ordering by more than one column, the second column is only used if the values in the first column are equal:
SELECT column_name(s)  FROM table_name ORDER BY column1, column2

cast-and-convert




Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ] )
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )


expression
Is any valid expression.
data_type
Is the target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used. For more information about available data types, see Data Types (Transact-SQL).
length
Is an optional integer that specifies the length of the target data type. The default value is 30.
style
Is an integer expression that specifies how the CONVERT function is to translate expression. If style is NULL, NULL is returned. The range is determined by data_type. For more information, see the Remarks section.

String Functions

1. CHARINDEX 
string function takes 2 arguments. 1st argument specifies the character whose index is to be retrieved and 2nd argument takes as a string from which character index is carried out.
Example:
Select CHARINDEX ('S','MICROSOFT SQL SERVER 2000')
Result: 6


2. LEFT 
string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns first characters of specified length starting from the left side of the string entered as 1st argument.
Example:
Select LEFT ('MICROSOFT SQL SERVER 2000',4)
Result: MICR


3. RIGHT 
string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns last characters of specified length starting from the right side of the string entered as 1st argument.
Example:
Select RIGHT ('MICROSOFT SQL SERVER 2000',4)
Result: 2000


4. LEN 
string function takes 1 argument as string value and returns the length of entered string.
Example:
Select LEN ('MICROSOFT SQL SERVER 2000')
Result: 25


5. REPLACE
  string function takes 3 arguments.
1st argument as string value.
2nd argument is a part of string entered as 1st argument which is to be replaced.
3rd argument as a new string value that is to be placed at the place of 2nd argument.
Example:
Select REPLACE ('MICROSOFT SQL SERVER 2000','MICROSOFT','MS')
Result: MS SQL SERVER 2000




6. STUFF 
string function takes 4 arguments. It is used to replace specified length of characters with provided pattern.
1st argument as string value.
2nd argument as integer value specifying the starting point of characters to be replaced.
3rd arguments as integer value specifying the length of characters.
4th argument as string value specifying the new pattern of characters.
Example:
Select STUFF ('MICROSOFT SQL SERVER 2000', 11, 3,'S.Q.L.')
Result: MICROSFT S.Q.L. SERVER 2000


7. SUBSTRING 
string function returns the sub string of specified length starting from the entered start position. It takes 3 arguments.
1st argument as string value.
2nd argument as integer specifying the start position.
3rd argument as integer specifying the length
Example:
Select SUBSTRING ('MICROSOFT SQL SERVER 2000', 11, 3)
Result: SQL

 
8. LOWER 
string function returns the lower case string whether the entered string has upper case letters. It takes 1 argument as string value.
Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING’)
Result: microsoft asp .net web hosting


9. UPPER
  string function returns the upper case string whether the entered string has lower case letters. It takes 1 argument as string value.
Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING with SQL Database’)
Result: MICROSOFT ASP .NET WEB HOSTING WITH SQL DATABASE

 
10. REVERSE 
string function returns the string in reverse order. It takes 1 argument as string value.
Example:
select REVERSE(‘ASP.NET’)
Result: TEN.PSA




11. LTRIM 
function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.
Example:
select LTRIM (‘ ASP ’)
Result: ASP-----
blanks at the right side not removed.

 
12. RTRIM 
function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.
Example:
select RTRIM (‘ ASP ’)
Result: -----ASP
blanks at the left side not removed.


13. PATINDEX 
function returns the position of first occurrence of specified pattern in the provided string. It takes 2 arguments.
1st argument as string value specifying the pattern to match
2nd argument as string value specifying the string to compare.
Example:
select PATINDEX('%RO%','MICROSOFT')
Results: 4


14. STR
function returns character data converted from numeric data. It takes 3 arguments.
1st argument as float data
2nd argument as integer value specifying the length of the string including decimal that is to be retrieved.
3rd argument as integer specifying the number of places to the right of the decimal point.
Example:
select STR(140.15, 6, 1)
Result: 140.2


15. ASCII  
function returns the ASCII code value from the leftmost character specified character expression. It takes 1 argument as string/character expression.
Example:
select ASCII('A')
Result: 65