Best Online Tutorials

SQL Tutorial

Free SQL Tutorial

SQL Interview Questions

SQL Practice Questions

SQL Tips & Tricks

SQL Errors

SQL Books

Angular Tutorial

Free Angular Tutorial

Angular Interview Questions

Angular Practice Questions

Angular Tips & Tricks

Angular Errors

Angular Books

Node.js Tutorial

Free Node.js Tutorial

Node.js Interview Questions

Node.js Practice Questions

Node.js Tips & Tricks

Node.js Errors

Node.js Books

React.js Tutorial

Free React.js Tutorial

React.js Interview Questions

React.js Practice Questions

React.js Tips & Tricks

React.js Errors

React.js Books

SSIS Tutorial

Free SSIS Tutorial

SSIS Interview Questions

SSIS Practice Questions

SSIS Tips & Tricks

SSIS Errors

SSIS Books

SEO Tutorial

Free SEO Tutorial

SEO Interview Questions

SEO Practice Questions

SEO Tips & Tricks

SEO Errors

SEO Books

Javascript Tutorial

Free Javascript Tutorial

Javascript Interview Questions

Javascript Practice Questions

Javascript Tips & Tricks

Javascript Errors

Javascript Books

MongoDB Tutorial

Free MongoDB Tutorial

MongoDB Interview Questions

MongoDB Practice Questions

MongoDB Tips & Tricks

MongoDB Errors

MongoDB Books

Next.js Tutorial

Free Next.js Tutorial

Next.js Interview Questions

Next.js Practice Questions

Next.js Tips & Tricks

Next.js Errors

Next.js Books

SQL Interview Questions | Part 5 

Prashant Surya  |   Jan 5, 2020  |  Views: 1678
pictures

Description:


What is Stored Procedure in SQL Server?


Ans : Stored Procedure is code in SQL which stored as object in a DB Server. It is group of one or more SQL statements.


It can accept parameter so that procedure can return values based on parameter. You can pass the one or multiple parameter.


Main Point of Stored procedures are follows:

  •    Stored procedure can return multiple values at a time.
  •    You can use DML statements such as SELECT, INSERT, DELETE and UPDATE in a single stored procedure.
  •    Stored procedures are in Precompilations form.
  •    You can handle exception by using TRY-CATCH block in Stored Procedure.
  •    You can call User-Defined Function in a stored Procedure.

Why we need stored procedure?


Ans : We need to use the stored procedure by the following reasons :

1)      Stored Procedure provide security when you are generating inline query into the web application, If you are using SP in application, your data would be secure.

2)      You can call multiple or single statements in single stored procedures.

3)      Stored procedures can be shared in multiple web applications so that we would not need to call same query in different application because each applications uses same stored procedures.

4)      Stored Procedure has executing permission which can be provide to particular user or role.

 


How to view the text of stored procedure?


Ans: You can view the text of stored Procedures by the given statement.


Syntax: SP_HelpText Procedure_name

For Example :


 sp_helptext GetCustomerDetail

           

How to encrypt the text of stored procedure?


Ans: You can encrypt the text of stored Procedures by using WITH ENCRYPTION.

 

For Example : You have created SP with encrypted option by using below statement.

 

CREATE PROCEDURE GetCustomerDetail
WITH ENCRYPTION
AS
BEGIN
     PRINT 'There are CustomerDetails'
END

 

Now if you want to view the text of SP, It is not possible to view. Message will be displayed as:


The text for object 'GetCustomerDetail' is encrypted.

 

How to recompile the stored procedure?


Ans: You can recompile the stored Procedures by using WITH RECOMPILE.

 

For example : You have created SP with Recompile option by using below statement.

 

CREATE PROCEDURE GetCustomerDetail
WITH Recompile
AS
BEGIN
     PRINT 'There are CustomerDetails'
END

 

If the procedure is created by using with Recompile option, it gets a new execution plan every time it runs.

 


How to call the stored procedure inside other stored procedure?


Ans: You can follow below steps to call stored procedure inside other stored procedure:

 

Step 1: Let’s suppose you are creating below procedure:

 

CREATE PROCEDURE GetCompanyDetail
AS
BEGIN
     PRINT 'There are CompanyDetails'
END

 

Step 2: Create Second SP to execute first SP.

 

CREATE PROCEDURE SP_ShowComapnies
AS
BEGIN
     exec GetCompanyDetail
END

 

Step 3: Now execute the second SP using the following query. It will execute the first SP and return the result. 

 

EXEC SP_ShowComapnies

 

Step 4: Output will be shown as below:

 

There are CompanyDetails

 

 

Interview Questions and Answers Videos


Types Of databases


DBMS Interview Questions and Answers


DBMS Interview Questions and Answers Part 2


Top 50 SQL Interview Questions and Answers





Comments:
Content Contributor
Prashant Surya

Prashant Surya

Software developer at Uniserve Data Technologies Pvt. Ltd.  |   Noida, Uttar Pradesh, India

I am a software developer, freelancer and trainer working on technologies likes. MEAN stack, MERN Stack, ASP.NET full stack, WebRTC, socket.io ,PWA, SQL, JavaScript, jQuery, HTML, CSS, SEO, responsive UI/UX designing... having 7 years of experience in development in multiple domains like, eCommerce, RTA, capital markets, education, real estate...


Read related articles

SQL Interview Questions | Part 5

Prashant Surya  |   Jan 5, 2020  |  Views: 1679

SQL Interview Questions | Part 4

Prashant Surya  |   Nov 2, 2019  |  Views: 1357

SQL Interview Questions | Part 3

Prashant Surya  |   Oct 30, 2019  |  Views: 1388

SQL interview questions | Part 2

Prashant Surya  |   Oct 24, 2019  |  Views: 1299

SQL interview questions | Part 1

Prashant Surya  |   Oct 22, 2019  |  Views: 2463

Want to sell your software ?-

If you want to sell your readymade software to the genuine clients or businessman, list your software with details and demo links.

  • Sell ERP
  • Sell Website Clones
  • Sell App Clones
  • Sell Readymade Portals
  • Sell Readymade Websites
  • Sell Mobile Apps
  • Sell Window Apps

Clients will find it using our advanced search filter and will contact you directly.

No any charge for the product lsiting.

Do you own a Company ?+

Property.sale

Buy or Sell Commercial Property+

Are you a freelancer ?+

Looking for a job ?+

Are you a Service Provider ?+

Are you a blogger ?+

Do you own an IT Institute ?+



Property.sale