Stored procedure in SQL Server: 

Stored procedure is a set of Transact-SQL statements compiled into a single execution plan. You can use stored procedure with input and output parameters.

There are basically two types of stored procedures:

a.   System stored procedures
b.   User defined stored procedures. 

Types: 

1.  User defined stored procedures:

A user-defined procedure is stored procedures created by user on user-defined database or in all system databases except the Resource database.

2.  System stored procedures:

 System stored procedures are sql server inbuilt stored procedures. All system stored procedures start with prefix _sp.

3.  Temporary Stored procedures:

 Temporary stored procedures are user defined stored procedures stored in tempdb. Two types of temporary stored procedure Global and local.

4.  Extended User-Defined

Extended procedures enable creating external routines in a programming language such as C. These procedures are DLLs that an instance of SQL Server can dynamically load and run

  Sql server stored procedure return data in four ways:

1.  Return codes, which are always an integer value.
2.  Result set from sql statement or stored procedure called by other stored procedure.
3.  A global cursor that can be referenced outside the stored procedure
4.  Using output parameters

  Advantages using stored procedure :

a. Group of SQL statement you can reuse.
b. Stored procedures can also improve performance.
c. Consistent, safe data modification
d. Improved security compared with inline t-sql query.
e. Sharing of application logic between applications.
f. Centralized code easy for modification and maintenance.
g. Stored procedures are cached on the server
h. Execution plans for the process are easily reviewable without having to run the application

--SQL Server Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name
[ ; number ] 
    [ { @parameter [ type_schema_name. ]
data_type }
        [ VARYING ] [ = default ] [ OUT |
OUTPUT | [READONLY]
    ] [ ,...n ] 
[ WITH
<procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]
 
<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [
EXECUTE AS Clause ]

CLR stored procedure syntax:

--SQL Server CLR Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name
[ ; number ] 
[ ; number ]
    [ { @parameter [ type_schema_name. ]
data_type }
        [ = default ] [ OUT | OUTPUT ]
[READONLY]
    ] [ ,...n ]
[ WITH EXECUTE AS
Clause ]
AS { EXTERNAL NAME
assembly_name.class_name.method_name }
assembly_name.class_name.method_name }[;]

Stored procedure with input parameters only:

--=============================================
--Author:Nagnath Kendre
--Create date: 19/04/2015
--Description:Get order details for specific user(input parameter) 
--=============================================
CREATE PROCEDURE GetOrderDetilsByUserId_SP
 -- Add the parameters for the stored procedure here
    @userId int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
--interfering with SELECT statements.
       SET NOCOUNT ON;
 
 SELECT U.LoginName AS Name,OrderId,OrderDate,Total,
 CASE HasBeenShipped 
 WHEN 0 THEN 'NO'
 ELSE 'YES'
 END AS [HasBeenShipped]
 FROM  [User] as U
 INNER JOIN [Order] AS O ON U.id=O.UserId WHERE O.UserId=@userId ORDER BY ORderID ASC
END
GO
 

Stored procedure with output parameters:

--=============================================
--Author:Nagnath Kendre
--Create date: 01/05/2015
--Description:Get order details for specific user(input parameter) 
--and Return output with @TotalOrders(Output Parameter)
--=============================================
Alter PROCEDURE GetOrderDetilAndTotalCountByUserId_SP
-- Add the parameters for the stored procedure here
            @userId int,
            @TotalOrders int
output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
--interfering with SELECT statements.
            SET NOCOUNT ON;
            
---Output parameter value     
SELECT @TotalOrders= sum(OrderId) from [Order] where UserId=@userId
 
--Order Detail result
 SELECT U.LoginName AS Name,OrderId,OrderDate,Total,
 CASE HasBeenShipped 
 WHEN 0 THEN 'NO'
 ELSE 'YES'
 END AS [HasBeenShipped]
 FROM  [User] as U
 INNER JOIN [Order] AS O ON U.id=O.UserId WHERE O.UserId=@userId ORDER BY ORderID ASC
END
GO
 

Stored procedure with table valued parameters:

--=============================================
--Author:Nagnath Kendre
--Create date: 01/05/2015
--Description: When updating Catagories update catagory by deleted date 
--=============================================
Alter PROCEDURE [dbo].[DeleteProductList_SP]
            @TVP DBO.ProductDeleteType
READONLY
AS
BEGIN
                        
    UPDATE [dbo].Products
    SET [UpdatedDate] = Getdate()
       ,[DeletedDate]= Getdate()
             FROM dbo.Products PC INNER JOIN @TVP AS TC
    ON PC.ProductID = TC.Id 
            
END
GO
 

CUBE operator:

The CUBE operator generates a result set that is a multidimensional cube. The CUBE operator is specified in the GROUP BY clause of a SELECT statement. His select list contains the dimension columns and aggregate function expressions.

   AS a sales manager I want to know order summery of total amount spent by user wise, Product category wise  for this summery we require to use CUBE operator with Group by clause and sum aggregate function .

   Difference b/w CUBE and ROLLUP:
·         CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.
·         ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

Example:

SELECT
LoginName AS Name,CategoryName
AS Category ,ProductName
AS Product,
SUM(Total) AS SubTotal FROM [order] O 
INNER JOIN [user] U ON O.userid=U.id
INNER JOIN OrderDetail D ON O.OrderId=D.OrderId
INNER JOIN Products P ON D.ProductId=P.ProductID 
INNER JOIN Categories C ON
P.CategoryID=C.CategoryID
GROUP BY ProductName,CategoryName,LoginName
WITH CUBE

Query returns amount spent sub total for User, in next column query returns sub total for product category and in last Create Total for all. 

Rollup:

The ROLLUP, CUBE, and GROUPING SETS operators are extensions of the GROUP BY clause. The ROLLUP operator is useful in generating Summery, reports that contain subtotals and totals. ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

   In web shop as a finance manager I want to know what subtotal and total amount of products by Categories.

Example:

Products table Data:

Rollup Query:

SELECT
    CASE WHEN (GROUPING(P.ProductName) = 1) THEN 'Sub Total'
       ELSE ISNULL(P.ProductName, 'UNKNOWN')
    END AS ProductName,
    CASE WHEN (GROUPING(CategoryName) = 1) THEN 'ALL'
       ELSE ISNULL(CategoryName, 'UNKNOWN')
    END AS Category,
       SUM(UnitPrice) AS TotalAmount
FROM
Products P
INNER JOIN Categories C ON
P.CategoryId=C.CategoryId  AND P.CategoryId IS NOT NULL
GROUP BY CategoryName,ProductName
WITH ROLLUP 

Note: ROLLUP do not support the CHECKSUM_AGG function.

   Advantages using over COMPUTE BY:
1.      ROLLUP returns single result set, COMPUTE BY returns multiple result sets, reason ROLLUP is simple       to use and efficient. 
2.      You can use ROLLUP with cursor while COMPUTE BY cannot.
3.      More efficient than COMPUTE BY.