Stored Procedure

Stored Procedure

Stored Procedure is one of the powerful parts of SQL Server. Basically it is a group of T-SQL statements which is complied and stored inside the SQL Server.

As a result we get some advantages like:
1. Secure due to encryption.
2. We can use repeatedly after once compile.
3. Reduces data pass over a network.
4. Secured raw data because they are accessible by only stored procedure.

Basis Syntax of Stored Procedure

CREATE PROCEDURE Object Name
Input parameter DataType
Output parameter DataType Output
AS
BEGIN
--Variable Declaration 
  @parameter DataType
--SQL Statements
……………………..
END

In the stored procedure we can use SELECT, INSERT, DELETE and UPDATE these four basic query statements. For example, we can use the following query from application.

SELECT EmpID,EmpName,Cell 
FROM Employee
WHERE DeptID = 1

So, every time this query will be compiled. But if we make a stored procedure then it will compile one time and can be used as many times as required. So, stored procedure is more efficient than general query.

Simple Stored Procedure

CREATE PROCEDURE sp_Employee 
@DID int 
AS
BEGIN
 SELECT EmpID,EmpName,Cell 
 FROM Employee
 WHERE DeptID = @DID
END

In above Stored Procedure @DID is a parameter.

Execute the Stored Procedure

EXEC sp_Employee 1
Or
EXECUTE sp_Employee 1

Random Number in SQL

How to generate unique random number in SQL?

Random Number in SQL

SQL Server has a built-in function to generate random number. The function is RAND(). It is a mathematical function.  It returns a random float value between 0 and 1.  We can also use an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value.To use it, we need to use a simple SELECT statement as follows:

SELECT RAND() AS [RandomNumber]

The output of the RAND() function will always be a value between 0 and 1.  If we want to generate a random integer number, we have to multiply it by the maximum value that we want to generate and then get rid of the decimal places.  By casting to integer we can get rid of the decimal places.

SELECT CAST(RAND() * 1000000 AS INT) AS [RandomNumber]

If we pass seed parameter value then the RAND() function will always return same value. 

SELECT RAND(1) AS [RandomNumber]

The NEWID() Way

Here's a different way to generate a random number without using the RAND() function. We can use NEWID system function to generate a random numeric value.

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS[RandomNumber]

The NEWID function returns a unique value of unique identifier data type. To convert it into integer first we have to convert it into VARBINARY then integer. The resulting integer value can be positive and negative. It we want only a positive value then we need to use the absolute value mathematical function ABS. 

Method 1 : Generate Random Numbers (Int) between Range

-- Variables for the random number

DECLARE @Random INT;

DECLARE @Upper INT;

DECLARE @Lower INT

-- Random number between 1 and 999

SET @Lower = 1     -- The lowest random number

SET @Upper = 999 -- The highest random number

SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower),0)

SELECT @Random