SQL Day 18: Stored Procedure in SQL Server

LO1: What is a Stored Procedure and creating a simple SP

LO2: Altering a Stored Procedure

LO3: Getting the text of Stored Procedure

LO4: Dropping the Stored Procedure

LO5: Encrypting the text of Stored Procedure


A stored procedure is a group of T-SQL (Transact-SQL) statements.

Let’s say we have a situation where we need to execute a query or queries, again and again, we can save that specific query in a stored procedure and call it just by its name.

Now we need the employee name and email from tblEmployee table so now we will wrap this query in an SP and execute the SP when required to get the result.

Creating a Simple Stored Procedure

We can use the Create Procedure command, the syntax is as followed.
Syntax: 

CREATE PROCEDURE 
AS
BEGIN
------Query-------
END

Example:

CREATE PROCEDURE spGetEmployeeName
AS
BEGIN
select name, email from tblEmployee
END


Once the above command gets executed the SP will be created in the database and will display under the Programmability > Stored Procedures folder as below.



We can also create the SP using Create Proc command, both are the same only naming is different.

Syntax:

CREATE PROC ProcedureName
AS
BEGIN
------Query---------
END



Note: When naming the user defined SP, Microsoft recommends not to use sp_ as a prefix because all the system defined SP are prefixed with sp_.

Executing the Stored Procedure

To execute the SP, we have three commands as follows.

  • Just executing the SP name i.e. write spGetEmployeeName and executing the name only, will execute the created SP.
  • Using EXEC <ProcedureName> command. For example EXEC spGetEmployeeName
  • User EXECUTE <ProcedureName> command. For example EXECUTE spGetEmployeeName

Altering a Stored Procedure

To alter an SP we can use the alter command as below and updated the statement as required.

ALTER PROC spGetEmployeeName1  
AS  
BEGIN  
select name, email, genderid from tblEmployee  
END



Yes, we can also use the PROCEDURE in place of PROC in above statements.

Getting the text of Stored Procedure

There are two ways to get the text of the SP.

  • Graphically

  • Using sp_helptext system stored procedure.

sp_helptext spGetEmployeeName


Dropping the Stored Procedure

To drop an SP we can use the drop command same as to drop the table

Syntax: DROP PROC <ProcedureName>


This will drop the created procedure.

Encrypting the text of Store Procedure

It is possible to encrypt the text of the SP so that no one can see the actual content of the SP using sp_helptext or by using graphically.

To encrypt the SP content we need to add WITH ENCRYPTION keywords while creating or altering the SP.

Alter PROC spGetEmployeeName WITH Encryption
AS  
BEGIN  
select name, email, genderid from tblEmployee  
END



Now if we try to get the text of the SP using sp_helptext, SQL will display a message “The text for object 'spGetEmployeeName' is encrypted.”


That’s all for today. Thank You.

Comments

Post a Comment

Popular posts from this blog

SQL Day 11: Group By in SQL Server

Image
LO1: Grouping rows using Group By LO2: Filtering Groups LO3: Difference between Where and Having Clause Group By clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expression. It is always used in conjunction with one or more aggregate functions. Let’s say we have a table tblEmployee which contains all the salary of employees including the cities. So if we need to find the maximum salary of employee we can use the MAX(Column_Name) aggregate function to find the highest salary of employees. select MAX(salary) from tblemployee In the same way, we can find the minimum salary and total salary of all the employees using different aggregate functions. select MIN(salary) as [Minimum Salary] from tblemployee select SUM(salary) as [Total Salary] from tblemployee Let’s say we have to find out the employee salary as per the different cities, to achieve this we need to use the Group B...
Keep reading

JAVA Script Executor Code to Click on an Element

Click on an Element in Selenium WebDriver Sometimes click() method does not work because two views are loaded at the same time on a page. In this case, we can use the JavascriptExecutor to get the work done. Below is code to click on an element using Javascript Executor. 1 2 3 4 5 6 7 @FindBy(id = Checkbox. financialAssistanceAccountEmailId ) public WebElement financialAssistanceAccountEmail; //Click to element JavascriptExecutor executor = (JavascriptExecutor) driver; executor. executeScript ( "arguments[0].click()" , financialAssistanceAccountEmail);
Keep reading

SQL Day 2: Creating, Altering and Dropping a Database

Image
LO1: Creating, Altering and Dropping a Database Prerequisite:   Connecting to SQL Server using SSMS After connecting to the database through SSMS first we need to create the database on which different queries will run. Let’s start with creating a Database. A SQL Server Database can be Created, Altered or Dropped in two ways: Graphically using SQL Server Management Studio or Using SQL Query Graphically using SQL Server Management Studio: Within the Object Explorer, right-click on Database folder and select the New Database. When clicked on New Database a new dialog box will open. Here you need to provide the database name and when clicking on OK, SQL Server run the query internally and will create the new database for us. When you expand the database folder then you will see the Sample database here Creating Database Using Query: We can also create the database by executing the query in the query editor. For creating a...
Keep reading