Hola friends, let’s understand what are stored procedure and the benefits of using it. I hope you have learnt some basics of SQL before this. Learn basics of SQL in my previous post. Also, if you want to learn about Mathematical Functions in SQL: ABS, Round, Trunc, Power Function
What is a Stored Procedure?
Stored procedures are a set of instructions that need to be instructed again and again. In SQL we can save this frequent instructions as a procedure and call them by just their name.
How to create Stored Procedures?
To create stored procedures, simply use the Create Proc/Procedure Procedure_name command.
We can also pass parameter in a stored procedure. The parameters can be of two type:
1. Input (Used to take input)
2. Output (used to print output) – The output keyword must be specified as output in the description.
One example of the stored procedure is shown in the below image:
Running the Stored Procedures:
The command used to execute a stored procedure is: EXEC|Execute Procedure_name
e.g. Exec HumanResouces.uspFindEmployee “123” (Here 123 is the value for the @BusinessEntityID input parameter.
Stored Procedure with Output Parameter
An example of output parameter is:
Create Proc spGetEmployeeCount
@LastName int
@EmployeeCount Output
As
Begin
Select @EmployeeCount =Count(*) from emp where LastName = @LastName
End
Executing Stored Procedure with Output Parameter:
To execute the stored procedures with output parameters, it is very important to declare the output variable first. To declare the output variable, use the following command:
Declare @OutputVariableName Datatype
Exec Storedprocedurename Input parameter, Output Parameter Out|Output
Print@OutputVariableName
e.g.
Declare @TotalCount Int
Exec spGetEmployeeCount @LastName = “Male”, @TotalCount Out
Print@TotalCount
Benefits of Stored procedures:
- The execution plan can be reused
When a normal statement is executed, the path like first few columns are selected, then refined through where clause and then ordered. This is called a kind of execution plan. So, we can save time through stored procedures.
e.g. Select name, gender from Emp
when id IN(12, 14, 15)
order by name
A normal statement if executed with different parameters, use different execution path. However Stored procedures uses the same execution path even with different variable values.
2. Reduced Network Traffic
Since Stored procedures allows code re-usability, the sql instructions takes less time and space while utilizing network bandwidth. Thus network traffic is reduced.
3. Easy Maintainability using Stored Procedures
Maintenance becomes easy. A change in stored procedure is easy rather than finding similar statements at various places and then modifying the code.
4. Code Re-usability
The instruction that will be required again and again need not to be typed again. We just have to create a stored procedure with that statement and this will help us in reusing the code in less time and space.
5. Better Security
The database can be huge and we don’t want everyone to give access to every user in the network. So, build procedures on the specific table to which the user wants access. This way we will be able to provide better security.
6. Avoid SQL Injection Attack
Stored procedures also helps in avoiding SQL injection attacks. To know more about SQL injection attacks, go to the link.
Learn basics of SQL here
String Functions in SQL
- Left function:
This string function in sql returns integer values from the left.
command: select left(string, integer count)
e.g. Select left(“abcd”, 3) – Result will be “abc”
2. Right Function
This string function in sql returns integer values from the right of a string/column value.
command: select left(string/column, integer count)
e.g. Select left(“abcd”, 3) – Result will be “bcd”
3. CharIndex
This sql function returns the index of the character value.
command: Select charindex(“a”, “abcd”) – answer would be 1
4. Len
This function the total length of a string or column value of string type.
command e.g. : Select len(“abcd”) – answer would be 4
Note: This sql string function will not count the blank values at the end of a string.
5. Substr
This sql function is used to select a substring value from a string.
Command: select substr(“string value”|column name, position to start, length of fetch character
e.g. Select substr(“abcd”, 1, 3) – answer would be “abc”
We can also choose negative indexing here. A -1 indicates from the right.
6. Replicate
Replicate function replicates a specific string to the specified number of times.
command: Select replicate(“string”, number of times to replicate)
e.g. Select lastname + replicate(“*”,5)
This command will repeat the * five times in lastname. Let’s say the lastname is “John” then the output is: “John*****”
6. Space
This sql function will insert space to the specified number of times between column values.
command: Select Lastname + Space(5) +First name – This command will introduce a 5 character space between first name column value and last name.
7. PatIndex (or Pattern Index)
PatIndex works the same way as charindex by telling the first occurrence. However, PatIndex allows you to use wildcard. You can’t use wildcard with CharIndex.
e.g. Select PatIndex(“aaab”, “abcaaababc”) – The answer would be 4
If it do not find any matched string, the sql string function returns zero.
8. Replace
This string function replaces a string into another one.
Command: Select(String, “value to replace”, replacement value”)
e.g. Select(“abcd.com”, “.com”, “.net”) – The result will be “abcd.net”
9. LTRIM
This string function is used to trim leading blanks from the beginning.
Command e.g. : Select LTRIM(LastName)
10. RTRIM:
This sql function trims the trailing blank values.
e.g. Last Name : “abcd ”
Select RTRIM(LastName) – the result will be “abcd”
11. ASCII:
This SQL function returns the ASCII code of the string value.
Command: Select ASCII(string value)
e.g. Select ASCII(A) – The answer would be 65
12. CHAR
This SQL function returns the character value from the integer ASCII code.
Command: Select CHAR(Integer value)
e.g. Select CHAR(65) – The answer would be A
13. STUFF
This sql function replaces a specific string value at the specified position by replacing the characters up to length value. It is kind of masking.
Command: Select STUFF(Column, Starting position, length, String to be used as replacement)
e.g. Select STUFF(LastName, 1, 3, “**”)
If the last name is “JohnMarcel”, the output of stuff function will be “***nMarcel”
Post Views: 2,207