Store procedure in SQL Server by Coderbaba

🎯Store procedure in MS SQL Server:

"Store procedure in asp.net c# SQL Server".
A stored procedure (SP) is a pre-compiled set of T-SQL statements, the basic purpose of which is to group T-SQL statements together to perform a task or set of multiple tasks. It is stored in the data dictionary and can be executed either from the SQL Server Management Studio or from within an application as required.


Stored procedures can execute batches of T-SQL statements, and return multiple result sets as well. It can accept input parameters and can return one or more output parameters to the calling procedure or batch. A stored procedure can call other stored procedures and can also return status information to the calling procedure to indicate whether they succeeded or failed.


🎯Benefits of a Stored Procedure:
--------------------------------
☑️Modular Programming –
--------------------
After a Stored Procedure is created, it can be invoked multiple times from multiple places from any application. If any modification is needed in the SP, it needs to be done only in one place. It increases code reusability.

☑️Improved Performance –
----------------------
Stored Procedures executes the code faster and reduces network traffic.


☑️Faster execution –
------------------
Stored procedures are pre-compiled i.e; parsed and optimized as soon as they are created and are stored in memory. Thus it executes a lot faster than sending many lines of SQL code from your application to the SQL Server. Doing that requires SQL Server to compile and optimize your SQL code every time it runs.


☑️Reduced network traffic –
-------------------------
Sending many lines of SQL code from an application over the network to SQL Server, impacts network performance. This is especially true if the SQL code is lengthy and is accessed frequently in the application. Running the code on the SQL Server (as a Stored Procedure) eliminates the need to send this code over the network. The only network traffic will be the parameters supplied ti invoke the SP and the results of any query. Stored Procedures limit data with WHERE clauses, ensuring that your application sends just the necessary data over the network wire.


☑️Security –
----------
Users can execute a stored procedure without needing to execute any of the SQL statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way. You can grant rights to the stored procedure without granting rights to the underlying objects.

Stored Procedures can be used to make SQL injection attacks more difficult to execute successfully.
Stored Procedures enable you to perform error-handling at the server.


🎯Syntax of a Stored Procedure:

CREATE PROCEDURE procedure_name

sql_Parameter

AS

Begin
sql_statement

End

------------------
🎯Example:


Create procedure [dbo].[PasswordRecovery]
(@username nvarchar(30),
@email nvarchar(30),
@password nvarchar(50) output,
@msg nvarchar(30) output)
as
begin
if exists(select *from Emp_login where user_id=@username and email_id=@email)
begin
select @password=password from Emp_login where user_id=@username and email_id=@email
set @msg='yes'
end
else
begin
set @msg='Credential dose not match. Retry!'
end
end

-----------------------——————-

🎯Types of Stored Procedures:
----------------------------—————

There are three types of Stored Procedures:

1☑️-User Defined Stored Procedures –
User Defined stored procedures are created by normal users like us.

2☑️-SQL Server System Stored Procedures –
System stored procedures usually begin with sp_ and most of them live in the Master database. They handle much of what is considered to be the nuts-and-bolts of SQL Server administration and are written in T-SQL just like user-defined SPs. Many vital functions of SQL Server rely on these SPs to remain intact!

3☑️-Extended Stored Procedures –
Extended stored procedures usually begins with xp_ or sp_ and also live in the Master database. They are not written in T-SQL. They are in fact compiled DLLs which open up a world of functionality to the SQL Server environment.

LINQ: Language Integrated Query
LINQ in DotNet (Short Notes)

IQVIA(www.iqvia.com) is hiring freshers who are passionate about programming for their development team in Bangalore .


Qualification
Bachelors or Masters in Computers
BE (Any degree)
2021 Pass out
70% & above

Skill Sets
C#
SQL Server
ASP.Net (Desired)

Evaluation
Aptitute Test – Online
Technical Test (including Coding) – Online
Technical Round – Face to Face (Teams)
Manager Round

Positions Required: 5

Target Joining date : 2nd Dec 2021


You can visit https://iqvia.wd1.myworkdayjobs.com/IQVIA/job/Bangalore-India/Associate-Software-Engineer_R1254241-1 and submit your resumes

1-Attendance System asp.net C# source code:
Link:https://imojo.in/19ChsSk

2 online examination system asp.net C# source code
link: https://imojo.in/16dfW71

1-Attendance System asp.net C# source code:
Link:https://imojo.in/19ChsSk

2 online examination system asp.net C# source code
link: https://imojo.in/16dfW71

What is SQL Keys?
👆👆👆