Define Stored Procedure with Example?

 Posted by Sriramnandha on 6/4/2012 | Category: Sql Server Interview questions | Views: 2668 | Points: 40
Answer:

Stored Procedure is an collection SQL Statements. To Reuse the code over and over again.
Stored Procedure is an Precompiled Execution. Stored Procedure have input and out put Parameters.
Stored Procedure Can reduce the Client /Server Network Traffic.
Explanation:
Before Creating store procedure whether insert data or select data..
In This example am using Adventure works DataBase

SELECT * FROM AdventureWorks.Person.Address


CREATE PROCEDURE GetAddress
AS
SELECT * FROM AdventureWorks.Person.Address
GO


EXEC GetAddress

--or just simply
uspGetAddress


To Create a Stored Procedure u can use Create Procedure or Create Proc
Just like you have the ability to use parameters with your SQL code you can also setup your stored procedures to except one or more parameter values.

CREATE PROCEDURE uspGetAddress @City nvarchar(30)

AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = @City
GO


CREATE PROCEDURE uspGetAddress @City nvarchar(30) 

AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City LIKE @City + '%'
GO


Default Parameter Values

CREATE PROCEDURE uspGetAddress @City nvarchar(30) = NULL

AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = @City
GO


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response