Stored Procedure

additional Resources:

Resources:
-ASP.NET Interview Questions
-http://www.VenkatASPInterview.blogspot.com
--c# Interview Questions
- http://www.VenkatCSharpInterview.blogspot.com
-http://csharp-video-tutorials.blogspot.com

Source: Stored procedures in sqlserver

***********************************************************************************

What is  a Stored Procedure?

Stored Procedure example

Creating a Stored Procedure with Parameters

Altering SP

Viewing the text of  the SP

Dropping the SP

***********************************************************************************

 Stored Procedure: A Stored procedure is a group of  T - SQL (Transact SQL) statements. If you 
have a situation, where  you write the same query over and over again, you can save that specific query as a stored procedure and call it just by it's name.

***********************************************************************************

1. Use CREATE PROCEDURE  or  CREATE PROC  statement tp create SP.

Note: When naming User defined stored procedures , Microsoft recommends not to use SP_ as a
prefix.All system  stored procedures, are prefixed with SP_.This avoids any ambiguity between User defined and system stored procedures and any conflicts, with some future system procedures.

***********************************************************************************

To execute the stored procedure

1.spGetEmployees
2.EXEC spGetEmployees
3.Execute spGetEmployees

Note:You can also right click on the procedure name,in Object Explorer in SQL Server Management Studio (SSMS) and select EXECUTE STORED PROCEDURE.


*********************************************************************************** 

 Select * from tblEmployee

Select Name, Gender from tblEmployee


***********************************************************************************

CREATE PROCEDURE spGetEmployees

 AS

 BEGIN

    Select Name,Gender from tblEmployee

END
 
***********************************************************************************

 Database

Programmability

[Execute] menu bar

   spGetEmployees

Benefits: Security , networktraffic

***********************************************************************************

Stored Procedure with Parameters :

Parameters and variables have an @ prefix in their name.

To execute:

EXECUTE spGetEmployeesByGenderAndDepartment 'Male',1

EXECUTE spGetEmployeesByGenderAndDepartment @DepartmentId = 1, @Gender = 'Male'



*********************************************************************************** 
 
To view  the text , of the stored procedure



1.Use System Stored Procedure:

           sp_helptext 'SPName'

OR

2. Right click the SP in Object Explorer ->Script Procedure as->Create To->New Query Editor Window


***********************************************************************************

To Change the stored procedure , use

ALTER PROCEDURE statement

To delete the SP, Use DROP PROC 'SPName' or  DROP  PROCEDURE 'SPName'

To encrypt the text of SP, use WITH ENCRYPTION option. It is not possible to view the text of
encrypted sp.

 ***********************************************************************************
Create proc SpGetEmployeeByGenderAndDepartment

@Gender nvarchar(20),

@DepartmentId int

AS

Begin

Select Name,Gender,DepartmentId from tblEmployee where Gender = @Gender and 
Department Id= @DepartmentId

End


 Note:The procedure expects the paramenter in the order if the order wise data not sent then if something that is expected as integer and a varchar is sent it errors out as variable of not that
data type, for character variable it changes the numeric to a variable.

example: 
 [Execute] - button on the menu
spGetEmployeesByGenderAndDepartment 1,'Male'

1 - converted to char  , 'Male' - can not change to int

EXECUTE spGetEmployeesByGenderAndDepartment @DepartmentId = 1, @Gender = 'Male'

Note:If you do not specify the parameter none, the order in which you send values matter.

 ***********************************************************************************


***********************************************************************************

Stored Procedure with Output Parameters Part 19:

Stored Procedures - Output Parameters

To create an SP with output parameters,we use the keyword OUT or OUTPUT.

Id        Name     Gender     DepartmentId
1         Sam        Male         1
2         Ram       Male         1         

3         Sara       Female      3
4         Todd       Male         2
5         John       Male         3
6         Sana       Female      2
7         James     Male        1

8         Rob         Male        2
9         Steve       Male       1
10       Palm        Female    2 

Create Procedure spGetEmployeeCountByGender
@Gender nVarChar(20),
@EmployeeCount int Output

as

Begin

Select  @EmployeeCount = Count (Id) from tblEmployee where Gender=@Gender
End

To execute the  stored procedure with output parameters:

Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender 'Male',@EmployeeTotal Output

Print @EmployeeTotal

Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender 'Male', @EmployeeTotal output

If you don't specify the output keyword it would be null.

The variable is not initialized it has null value.

Declare @TotalCount  int

Execute spGetEmployeeCountByGender @EmployeeCount=@TotalCount,@Gender='Male'

Print @TotalCount


***********************************************************************************
Useful System stored procedures:

sp_help procedure_name  - view the information about the stored procedure,like parameter names,their data types etc.sp_help can be used with any database object, like tables,views,sp's,triggers etc.Alternatively you can also press ALT+F1, when the name of the object is highlighted.

sp_helptext procedure_name - view the text of the stored procedure.

sp_depends procedure_name - view the dependencies of  the stored procedure
(very useful).


 ***********************************************************************************
1) sp_help spGetEmployeeCountByGender

note: with any database objects ( table,view,triggers)

sp_help  tblEmployee

tells:all pk,fk all details on table.

or highlight   tblEmployee  , press (ALT+F1)

 2)sp_helptext 
(text of stored procedure)

Encrypted stored procedure - you can not see it.

3)sp_depends spGetEmployeeCountByGender

If you want to drop table, then the stored procedure will show error.

sp_depends  tblEmployee  

-press F5 ,

show the stored procedure is dependant on it.

sp_depends : use with other database objects.


 **********************************************************************************

Stored procedure output parameters or return values Part 20

In this session we will learn:

-Understand what are stored procedure return values
-Difference between stored procedure

Return values & Output parameters:

-When to use output parameters over return values

Pre-requisite:

Part 18 - Stored Procedure in SQL Server
Part 19- Stored Procedure with Output Parameters

Next:
Part 21- Advantages of  Stored Procedure

Whenever, you execute a stored procedure , it returns an integer status variable.

Usually, Zero indicates success, and non-zero indicates failures.
---
Create Procedure spGetTotalCountOfEmployees1
@TotalCount  int output
as
Begin
Select @TotalCount = COUNT(ID) from tblEmployee
End
---
Declare  @TotalEmployee int
Execute spGetTotalCountOfEmployees @TotalEmployee output
Select @TotalEmployees
---
Create  Procedure  spGetTotalCountOfEmployees2
as
Begin
return(select COUNT(ID) from Employees)

End
---
Declare @TotalEmployees int
Execute @TotalEmployees = spGetTotalCountOfEmployees2
Select @TotalEmployees
---
So, we are able to achieve what we want , using output parameters as well as return values.
Now let's look at example, where return status variables can not be used, but output parameters
can be used.

Select * from tblEmployee
db.spGetNameById
----
Declare @return_value int, @Name nvarchar(20)

EXEC @return_value = [dbo].[spGetNameById]

@Id=1,@Name=@Name OUTPUT

Select @Name as N'@Name'

Select 'ReturnValue' = @return_value

GO
---
Note:One can use 'Select' or 'Print'

The stored procedure only returns integer value.So where a nvarchar value say employeename
is tried to be returned it would error out.If  two values have to be returned it can not work also as
it returns only one value.

Output Parameters or Return values


Return Status Value                             Output parameters
0 -success 
nonzero - not success
Only Integer data type                     any data type

Only one value                                  More than one value         

Use to convey success or
failure                                                Use to return values like name,count etc


---

In nested stored procedure , return value is required to evaluate to know on success and failure.

**********************************************************************************    

Advantages of stored procedures Part 21

**********************************************************************************

Next Session:

Part 22 - Built in string functions in sql server

Pre-requisite:
Part 18- Stored Procedure in SQL Server

**********************************************************************************
In this session we will learn

-Advantages of using stored procedure over adhoc queries( inline SQL )

1.Execution plan retention and reusability

2.Reduces network traffic

3.

 **********************************************************************************

**********************************************************************************
**********************************************************************************

 

 **********************************************************************************