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
Syntax:--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
Stored procedure with default parameters:
-- =============================================
-- Author: Nagnath Kendre
-- Create date: 13/10/2019
-- Description: Update user
-- =============================================
ALTER PROCEDURE [dbo].[UpdateC4UUser_SP]
@FirstName nvarchar(50)
,@LastName nvarchar(50)
,@Email varchar(100)
,@Administrator
bit
,@UserId int
–- Set activate to false as default parameter
,@ActivateUser
bit=0
AS
BEGIN
–-Default functionality
IF(@ActivateUser = 0)
BEGIN
Update [dbo].[C4uUsers]
SET
[FirstName]=@FirstName
,[LastName]=@LastName
,[Email]=@Email
,[IsAdministrator]=@Administrator
WHERE Id=@UserId
END
–-If activate user parameter passed as true then below functionality
ELSE
BEGIN
UPDATE [dbo].[C4uUsers] SET isDeleted=0,DeletedDate=null WHERE Id=@UserId
END
END