Store temporary data in Stored Procedure in SQL

We  often have a problem to store a set of temporary data within the procedure, which not persist beyond the scope of the procedure. In such situation, we have basically four ways through which we can "store" this temporary : local temporary tables (#table_name), global temporary tables (##table_name), permanent tables (table_name), and table variables (@table_name). 

     Each of the four table options has its own purpose and use, and each has its benefits and issues:

1. Normal tables are exactly that, physical tables defined in your database.
2. Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them.
3. Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. Both local temporary tables and global temporary tables are physical tables created within the tempdb database.
4. Table variables are stored within memory but are laid out like a table. Table variables are partially stored on disk and partially stored in memory. It's a common misconception that table variables are stored only in memory. Because they are partially stored in memory, the access time for a table variable can be faster than the time it takes to access a temporary table.

Temporary Table

We prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table. 

Example of Temp table:

CREATE TABLE #temp (

ID int,

FirstName varchar(30),

LastName varchar(30) )

INSERT INTO #temp (ID, FirstName,LastName)

values(1,'Pankaj','Rana')

select * from #temp

drop table #temp

Table Variables

If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data

Example of Table variable:

DECLARE @temp TABLE (

ID int,

FirstName varchar(30),

LastName varchar(30) )

INSERT INTO @temp (ID, FirstName,LastName)

values(1,'Pankaj','Rana')

select * from @temp

Note: Table variables don't need to be dropped when you are done with them.

Which to Use

·         If you have less than 100 rows generally use a table variable.  Otherwise use  a temporary table.  This is because SQL Server won't create statistics on table variables.

·         If you need to create indexes on it then you must use a temporary table.

·         When using temporary tables always create them and create any indexes and then use them.  This will help reduce recompilations.  The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.

Cursor in SQL with example

A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.

To use cursors in SQL procedures, you need to do the following:

1. Declare a cursor that defines a result set.
2. Open the cursor to establish the result set.
3. Fetch the data into local variables as needed from the cursor, one row at a time.
4. Close the cursor when done
5. Deallocate  the cursor

To work with cursors you must use the following SQL statements:

• DECLARE CURSOR
• OPEN
• FETCH
• CLOSE
• DEALLOCATE

Declare @Firstname varchar(50)

Declare @Lastname varchar(50)

DECLARE @Email varchar(20)

DECLARE Student_cursor CURSOR FOR

Select Firstname,Lastname,Email from from student

OPEN Student_cursor;

FETCH NEXT FROM Student_cursor

INTO @Firstname, @Lastname, @Email;

WHILE @@FETCH_STATUS = 0

BEGIN

print ‘---------------‘

print @Firstname

print @Lastname

print @Email

print ‘---------------‘

FETCH NEXT FROM Student_cursor

    INTO @Firstname, @Lastname, @Email;

END

CLOSE Student_cursor;

DEALLOCATE Student_cursor;

Check the status of Cursor

--Create a cursor.

DECLARE cur CURSOR

Display the status of the cursor after declare

SELECT CURSOR_STATUS('global','cur') return -1

 OPEN cur

Display the status of the cursor after open

SELECT CURSOR_STATUS('global','cur') return 1

CLOSE cur

Display the status of the cursor after close

SELECT CURSOR_STATUS('global','cur')return -1

--Remove the cursor.

DEALLOCATE cur

Data Integrity in Database

Data Integrity in Database

It is very important for the data in a database to be accurate, consistent, and reliable. This implies that it is very crucial to enforce data integrity.

Data integrity ensures the consistency and correctness of data stored in a database. It is broadly classified into the following four categories:

Entity integrity

Domain integrity

Referential integrity

User-defined integrity

Entity Integrity

 Entity integrity ensures that each row can be uniquely identified by an attribute called the primary key. The primary key cannot be NULL.

For example, there might be two students with the same name ‘John’. They can be identified using the unique code assigned to them.

Domain Integrity

Domain integrity ensures that only a valid range of values is allowed to be stored in a column. It can be enforced by restricting the type of data, the range of values and the format of the data.

For example, let’s assume a table called BranchOffices that stores the address of various offices of a company. Let’s also assume it has a column city that stores the name of cities where the branch offices are located. Assume that offices are located in  ‘Boston’, ’California’ ,’Dallas’, ’New York’, ’Washington’. By enforcing domain integrity we ensure that only valid values can be entered in the city column of the BranchOffice table.

Referential Integrity

Referential Integrity ensures that the values of the foreign key match with the values of the corresponding primary key.

For example, if a toy has been ordered and an entry is to be made in the OrderDetail table, the the code should exists in the Toys tables. This is to ensure that an order is being placed for the available toys.

User-defined Integrity

User-defined Integrity refers to a set of rules specified by a user, which do not belong to the entity, domain and referential integrity categories.

For example, you do not want a candidate who is less than 18 years to apply for a post.

Union in SQL

The SQL UNION is used to combine the results of two or more SELECT SQL statements into a single result. Each query statement should have same column structure: same number of columns, same or compatible data types and in same order. The columns in each SELECT statement must be in exactly the same order too. You have to keep the column order of all unionized SQL SELECT expressions uniform, otherwise you’ll get an error.

SQL UNION Query Syntax

SELECT ColumnName(s) FROM Table1  

UNION  

SELECT ColumnName (s) FROM Table2  

Note: By Default result  of UNION is distinct. If you want all record you have to use UNION ALL

SQL UNION Query Example

Table: TestingEmployee

EmployeeId	FirstName	LastName
203	Rishi	Rawat
204	Michel	Jones
205	Nathen	Astel
206	Monica	George

Table: DevelopmentEmployee

EmployeeId	FirstName	LastName
206	Monica	George
207	Anita	Paul
208	John	Pit
209	Jennifer	Bob

Select all the different employees in TestingEmployee and DevelopmentEmployee:

SELECT EmployeeId, FirstName, LastName FROM TestingEmployee

UNION  

SELECT EmployeeId, FirstName, LastName FROM DevelopmentEmployees  

The result will look like:

EmployeeId	FirstName	LastName
203	Rishi	Rawat
204	Michel	Jones
205	Nathen	Astel
206	Monica	George
207	Anita	Paul
208	John	Pit
209	Jennifer	Bob

Note: Record of EmloyeeID 206 is in both tables but in result it display only one time if we use UNION ALL then the result display duplicate records for EmployeeId 206

Differences between Stored Procedures and Functions

1. Procedure can return zero or n values whereas function can return one value which is mandatory.
2. Procedures can have input/output parameters for it whereas functions can have only input parameters.
3. Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
4. Functions can be called from procedure whereas procedures cannot be called from function.
5. Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6. We can go for transaction management in procedure whereas we can't go in function.
7. Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
9. UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
10. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
11. Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

How to Retrieve Last Inserted Identity of Record

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

Note:  Always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Stored Procedure in SQL

Stored Procedures

Stored procedure  is the block of SQL statements written together to performs some specific task.  A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block. A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.
We can pass parameters to procedures in three ways.
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters
A procedure may or may not return any value.

Advantage of Stored Procedure

There are multiple advantages to using stored procedures:

1. Stored procedures will generally perform better than the same group of statements executed individually. That is because when stored procedure code is compiled the execution algorithm is stored in the server procedure cache. Therefore, the SQL Server engine does not have to make a decision on how to run a stored procedure each time it is executed.
    
2. Stored procedures can provide a high level of security to SQL Server data. If you write a stored procedure for every Data Modification Language statement (SELECT, INSERT, UPDATE, DELETE), then you can grant the database users access to these stored procedures, without giving permissions to the underlying tables. This way you can tightly control what users can do with your data.
    
3. Stored procedures can accept and return parameters. Each stored procedure can have up to 2100 parameters.
    
4. Stored procedures let you encapsulate business rules within manageable components of code. When the business rule changes, you only have to modify your code in one place.
    
5. Stored procedures can be executed automatically when SQL Server starts up. Alternatively you can invoke stored procedures explicitly. 

Example of Stored Procedure

Create Proc [dbo].[Dinner_SelectEmployee]

as

Select Firstname, Lastname, DateofBirth,Department  from Employee

Use OUTPUT Parameter to Get data from Store Procedure

Create Stored Procedure

USE Northwind

CREATE PROC usp_InserShipper

@CompanyName      VARCHAR(100),

@Phone                  VARCHAR(12),

@ShipperID        INT         OUTPUT

AS

BEGIN

      INSERT INTO Shippers(CompanyName,Phone)

      VALUES(@CompanyName,@Phone)

     

      SET @ShipperID=SCOPE_IDENTITY();

END

Get Output Paramenter by executing stored procedure

DECLARE @ShipperID INT

DECLARE @CompanyName VARCHAR(100)

DECLARE @Phone          VARCHAR(12)

SET @CompanyName= 'My Company'

SET   @Phone=     '(503) 555-9831'

EXEC usp_InserShipper @CompanyName,@Phone,@ShipperID OUTPUT

SELECT @ShipperID  --Get ShipperID using Output parameter

String Functions in SQL

In SQL Server there are several built-in string functions to perform string manipulations a mainly used to change the case of strings, concatenate strings, reverse strings, extract various part of strings. All below functions takes string input value and return a string or numeric value.
ASCII : Returns the ASCII code value for a character
Syntax: ASCII(character)
SELECT ASCII('a') returns 97
SELECT ASCII('A') returns 65


For Upper character 'A' to 'Z' ASCII value 65 to 90
For Lower character 'A' to 'Z' ASCII value 97 to 122
For digit '0' to '9' ASCII value 48 to 57
UNICODE : UNICODE function works just like ASCII function, except returns Unicode standard integer value. UNICODE could be useful if you are working with international character sets.
Syntax: UNICODE(character)
SELECT UNICODE('F') returns 70
SELECT UNICODE('STRING FUNCTION') returns 83 (leftmost character of string)
LOWER : Convert character strings data into lowercase.
Syntax: LOWER(string)
SELECT LOWER('STRING FUNCTION') returns string function
UPPER : Convert character strings data into Uppercase.
Syntax: UPPER(string)
SELECT UPPER('string function') returns STRING FUNCTION
LEN : Returns the length of the character string.
Syntax: LEN(string)
SELECT LEN('STRING FUNCTION')returns15
REPLACE : Replaces all occurrences of the second string(string2) in the first string(string1) with a third string(string3).
Syntax: REPLACE('string1','string2','string3')
SELECT REPLACE('STRING FUNCTION','STRING','SQL') returns SQL Function
Returns NULL if any one of the arguments is NULL.
LEFT : Returns left part of a string with the specified number of characters counting from left.LEFT function is used to retrieve portions of the string.
Syntax: LEFT(string,integer)
SELECT LEFT('STRING FUNCTION', 6) returns STRING
RIGHT : Returns right part of a string with the specified number of characters counting from right.RIGHT function is used to retrieve portions of the string.
Syntax: RIGHT(string,integer)
SELECT RIGHT('STRING FUNCTION', 8) returns FUNCTION
LTRIM : Returns a string after removing leading blanks on Left side.(Remove left side space or blanks)
Syntax: LTRIM(string)
SELECT LTRIM('   STRING FUNCTION') returns STRING FUNCTION
RTRIM : Returns a string after removing leading blanks on Right side.(Remove right side space or blanks)
Syntax: RTRIM( string )
SELECT RTRIM('STRING FUNCTION   ') returns STRING FUNCTION
REVERSE : Returns reverse of a input string.
Syntax: REVERSE(string)
SELECT REVERSE('STRING FUNCTION') returns NOITCNUF GNIRTS
REPLICATE : Repeats a input string for a specified number of times.
Syntax: REPLICATE (string, integer)
SELECT REPLICATE(Repeat, 3) returns Repeat Repeat Repeat
SPACE : Returns a string of repeated spaces.The SPACE function is an equivalent of using REPLICATE function to repeat spaces.
Syntax: SPACE ( integer) (If integer is negative,a null string is returned.)
SELECT ('STRING') + SPACE(1) + ('FUNCTION') returns STRING FUNCTION
SUBSTRING : Returns part of a given string.
SUBSTRING function retrieves a portion of the given string starting at the specified character(startindex) to the number of characters specified(length).
Syntax: SUBSTRING (string,startindex,length)
SELECT SUBSTRING('STRING FUNCTION', 1, 6) returns STRING
SELECT SUBSTRING('STRING FUNCTION', 8, 8) returns FUNCTION
STUFF : Deletes a specified length of characters and inserts another set of characters at a specified starting point.
STUFF function is useful to inserts a set of characters(string2) into a given string(string1) at a given position.
Syntax: STUFF (string1,startindex,length,string2)
SELECT STUFF('STRING FUNCTION', 1, 6, 'SQL') returns SQL FUNCTION
SELECT STUFF('SQL FUNCTION', 5, 8, 'Tutorial') returns SQL Tutorial
CHARINDEX : Returns the starting position of the specified string(string1) in a character string(string2).
Syntax: CHARINDEX (string1,string2 [,start_location ])
SELECT CHARINDEX('SQL','Useful SQL String Function') returns 8
SELECT CHARINDEX('SQL','Useful SQL String Function') returns 19
If string1 is not found within string2,CHARINDEX returns 0.
PATINDEX : PATINDEX function works very similar to CHARINDEX function.PATINDEX function returns the starting position of the first occurrence of a pattern in a specified string, or zeros if the pttern is not found.
Using PATINDEX function you can search pattern in given string using Wildcard characters(%).The % character must come before and after pattern.
Syntax: PATINDEX('%pattern%',string)
SELECT PATINDEX('%SQL%','Useful SQL String Function') returns 8
SELECT PATINDEX('Useful%','Useful SQL String Function') returns 1
SELECT PATINDEX('%Function','Useful SQL String Function') returns 19
If pattern is not found within given string,PATINDEX returns 0.

Function	Parameters		Example			Description
ASCII	(character_experssion)		SELECT ASCII(‘A’)			Return 65, the ASCII code of the leftmost character ‘A’
CHAR	(integer_expression)		SELECT CHAR(65)			Return ‘A’ the character equivalent of the ASCII code value
CHARINDEX	(‘pattern’,expression)		SELECT CHARINDEX(‘E’,HELLO’)			Returns 2, the starting postion of the
DIFFERENCE	(character_expression1, character_expression2)		SELECT DIFFRENCE(‘HELLO’,’hell’)			Return 4. The DIFFRENCE function compares two strings and evaluates the similarity between them, return a value from 0 through 4. The value 4 is the best match.
LEFT	(character_expression, interger_expression)		SELECT LEFT(‘RICHARD’,4)			Return ‘RICH’ which is part of the character string equal in size to the integer_expression characters from the left.
LEN	(character_expression)		SELECT LEN(‘RICHARD’)			Returns ‘7, the number of characters in the character_expression.
LOWER	(character_expression)		SELECT LOWER(‘RICHARD’)			Returns ‘richard’, after converting Character_expression to lower case.
LTRIM	(character_expression)		SELECT LTRIM(‘   RICHARD)			Return ‘RICHARD’ without leading spaces. It  removes leading blanks from the character_expression.
PATINDEX		(‘%pattern%’,expression)		SELECT PATINDEX(‘%BOX%’,’ACTION BOX’)	Return 7, the starting position of the first occurrence of the pattern in the specified expression, or zeros if the pattern is not found.
REVERSE		(character_expression)		SELECT RIGHT(RICHARD’)	Returns ‘DRAHCIR’), the reverse of the character_expression.
RIGHT		(character_expression, integer_expression)		SELECT RIGHT(‘RICHARD’,4)	Returns ‘HARD’ , the part of the character string, extracting from the right the number of character specified in the integer_expression.
RTRIM		(character_expression)		SELECT RTRIM(‘RICHARD  ‘)	Returns ‘RICHARD’, after removing any trailing blanks from the character Expression.
SPACE		(integer_expression,		SELECT ‘RICHARD’+ SPACE(2)+’HILL’	Returns ‘RICHARD HILL’. Two spaces are inserted between the first and second word.
STR		(float_expression, [length,[decimal])		SELECT STR(123.45,6,2)	Returns ‘123.45’. It converts numeric data to character data where the length is the total length, incude the decimal point, the sign, the digits, and the spaces and the decimal is the number of places to the right of the decimal point.
STUFF		(character_expression1, Start,length, character_expression2		SELECT STUFF(‘Weather’,2,2,’i’)	Returns ‘Wither’. It deletes length character from character_expression1 from the start and then inserts char_expression2 into character_expression1 at the start position.
SUBSTRING		(expression,start,length)		SELECT SUBSTRING(‘Wether’,2,2)	Returns ‘ea’ which is part of a character string. It returns length characters from the start position of the expression.
UPPER		(character_expression)		SELECT UPPER(‘Richard’)	Retiurns ‘RICHARD’. It converts lower case character data to upper case.