Stored Procedure in SQL Server can be defined as the set of logically group of SQL statement which are grouped to perform a specific task.
We Use stored procedure is that it increases the performance of the database.
stored procedures is that you can centralize data access logic into a single place that is then easy for DBA's to optimize. Stored procedures also have a security benefit in that you can grant execute rights to a stored procedure but the user will not need to have read/write permissions on the underlying tables. This is a good first step against SQL injection.
</ br>
Step 1 : Firstly Create Open Computer Serach SQl Server Click and Open.
Step 2 : Create Databse like in Example as below.
Step 3 : Write this command in Sql Server and Run (F5) This Command. We Use This Db .
Step 4 : After that created Database Create New Table With Tbl_Employee Name.
Create Store Procedure in SQL Server
Step 1 : Firstly Create Open Computer Serach SQl Server Click and Open.
Step 2 : Create Databse like in Example as below.
EX.
CREATE DATABASE Db_Employee
CREATE DATABASE Db_Employee
Step 3 : Write this command in Sql Server and Run (F5) This Command. We Use This Db .
USE Db_Employee
Step 4 : After that created Database Create New Table With Tbl_Employee Name.
Ex.
CREATE TABLE Tbl_Employee
(
Id int,
Name NVARCHAR(50)
)
Step 5 : Created Table Then Create Procedure as Below -
CREATE TABLE Tbl_Employee
(
Id int,
Name NVARCHAR(50)
)
Syntex-
Create Procedure Sp_Name ( @paerameter ParameterType(Size) ) Write Sql QueryExample
In this example we will query the Tbl_Employee table from the Db_Employee database, but instead of getting back all records we will limit it to just a particular Employee. This example assumes there will be an exact match on the Employee value that is passed.
USE Db_Employee
GO
CREATE PROCEDURE dbo.SP_Employee
@Name nvarchar(30)
AS
SELECT *
FROM Tbl_Employee
WHERE Name= @Name
Note- Here SP_Employee Store Procedure Name
@Name is a Parameter and we give Multiple Parameter also.
Execute of Store Procedure
EXEC SP_Employee @Name= 'Ram'
How TO Alter Store Procedure
Syntex-
ALTER PROCEDURE Procedure_Name
EXAMPLE
ALTER PROCEDURE SP_Employee @Name= 'Ram'
Advantage Of Using Store Procedure
1. Using the Stored procedure is that it reduces the amount of information sent to the database server. 2.Compilation step is required only once when the stored procedure is created. Then after it does not require recompilation before executing unless it is modified and reutilizes the same execution plan whereas the SQL statements need to be compiled every time whenever it is sent for execution even if we send the same SQL statement every time. 3.Code Usability User Can Call Multiple time Store Procedure. 4.Using Store Procedure Maintain Security. 5.In SQL Server we have different types of stored procedures: System stored procedures User-defined stored procedures Extended stored Procedures System-stored procedures are stored in the master database and these start with a sp_ prefix. These procedures can be used to perform a variety of tasks to support SQL Server functions for external application calls in the system tables Example: sp_helptext [StoredProcedure_Name] User-defined stored procedures are usually stored in a user database and are typically designed to complete the tasks in the user database. While coding these procedures don’t use the sp_ prefix because if we use the sp_ prefix first, it will check the master database, and then it comes to user defined database. Extended stored procedures are the procedures that call functions from DLL files. Nowadays, extended stored procedures are depreciated for the reason it would be better to avoid using extended stored procedures.Count number of words in string through function in SQL
In This article make a function in sql server to find out the how may time word occurrence in string and you can count how many words present in this string through function in sql server
CREATE FUNCTION fnCountOccurences
(@ShortString VARCHAR(100),
@LongString VARCHAR(8000))
RETURNS INT
AS
BEGIN
DECLARE @Text VARCHAR(8000),
@Frequency INT
SET @Text = @LongString
SET @Text = REPLACE(@Text,@ShortString,'')
SET @Frequency = (len(rtrim(@LongString)) - len(rtrim(@Text)))
/ len(rtrim(@ShortString))
RETURN (@Frequency)
END
We Get OutPut:-
SELECT[dbo].[fnCountOccurences](',','a,b,c,c,')
Execute
OutPut- 4
(@ShortString VARCHAR(100),
@LongString VARCHAR(8000))
RETURNS INT
AS
BEGIN
DECLARE @Text VARCHAR(8000),
@Frequency INT
SET @Text = @LongString
SET @Text = REPLACE(@Text,@ShortString,'')
SET @Frequency = (len(rtrim(@LongString)) - len(rtrim(@Text)))
/ len(rtrim(@ShortString))
RETURN (@Frequency)
END
We Get OutPut:-
SELECT[dbo].[fnCountOccurences](',','a,b,c,c,')
Execute
OutPut- 4
