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
