SQL Server Stored Prcoedure

A stored procedure is a precompiled group of Transact-SQL statements that has been created and stored in the database. 

Benefits:

1)  Modular programming

2) Performance - Faster Execution and Reduced network traffic and Improved Security.
 

-- to create a new Store Procedure

CREATE PROCEDURE Procedure_name

@Username varchar(100)

AS

BEGIN

SELECT Username FROM USER_table WHERE USERNAME=@Username

END

·        Finally select all text and press F5

-- to ALTER or Modify an already existing Store Procedure

ALTER PROCEDURE Procedure_name

@Username varchar(100)

AS

BEGIN

SELECT Username FROM USER_table WHERE USERNAME=@Username

END

·        Finally select all text and press F5

--to view the stored procedure code

sp_helptext proc_name

--to execute the stored procedure code

EXEC Procedure_name  ‘argu1’,’argu2’,….

Or

Procedure_name  ‘argu1’,’argu2’,….

--to delete stored procedure from db

drop procedure [proc_name]

or

drop proc [proc_name]

Examples:

Branching within Stored Procedure

Create proc [dbo].[proc_name]

@domain nvarchar(100),

@location nvarchar(100) = ''

as

begin

IF(@domain = 'yahoo.com') --or (@domain = 'gmail.com')

BEGIN

SELECT DomainName= SUBSTRING(DomainName,CHARINDEX('@',DomainName)+1,50),ApproversEmailId,ApproversName, BankName   FROM InternalBank_Details

WHERE DomainName = 'test@' + @domain AND Location = @location

END

ELSE

BEGIN

SELECT DomainName= SUBSTRING(DomainName,CHARINDEX('@',DomainName)+1,50),ApproversEmailId,ApproversName  FROM OutBank_Details WHERE DomainName = 'test@' + @domain

END

end