Create Stored Procedure in SQL Server

Create Stored Procedure in SQL Server A stored procedure groups one or more Transact-SQL statements into a logical unit, stored as an object in a SQL Server database. Benefits of using stored procedures: Stored procedures help centralize your Transact-SQL code in the data tier. Websites or applications that embed ad hoc SQL are notoriously difficult to modify in a production environment. When ad hoc SQL is embedded in an application, you may spend too much time trying to find and debug the embedded SQL. Once you’ve found the bug, chances are you’ll need to recompile the page or program executable, causing unnecessary application outages or application distribution nightmares. If you centralize your Transact-SQL code in stored procedures, you’ll have only one place to look for SQL code or SQL batches. If you document the code properly, you’ll also be able to capture the areas that need fixing. Stored procedures can help reduce network traffic for larger ad hoc queries. Programming your application call to execute a stored procedure, rather then push across a 500 line SQL call, can have a positive impact on your network and application performance, particularly if the call is repeated thousands of times a minute. Stored procedures encourage code reusability. For example, if your web application uses a drop-down menu containing a list of cities, and this drop-down is used in multiple web pages, you can call the stored procedure from each web page rather than embed the same SQL in multiple places. Stored procedures are Secure with the data Security. Example Create Simple Procedure CREATE PROCEDURE [dbo].[Proc_GETCOUNTRY] AS BEGIN SET NOCOUNT ON; select Country_id, Country from tbl_Country order by Country END Now if you want to see the result from this procedure then execute it with this given command. EXEC Proc_GETCOUNTRY Result is Country_id Country 1 America 2 Canada 3 Brazil 4 Japan 5 India 6 China Alter Stored Procedures If You want to make some Changes in your Existing procedure then you will only get that procedure and with ALTER keyword you can alter your procedure .The Example is here. ALTER PROCEDURE [dbo].[Proc_GETCOUNTRY] AS BEGIN SET NOCOUNT ON; select top 2 Country from tbl_Country END Now Execute it with F5 key or you can Write EXEC Proc_GETCOUNTRY The Result is: Country America Canada Dropping Stored Procedures You can drop a stored procedure from the database using the DROP PROCEDURE command. The syntax for dropping a stored procedure is: DROP PROCEDURE { [ schema_name. ] procedure } [ ,...n ] This command takes one argument; the name of the procedure or procedures to drop. For example: DROP PROCEDURE dbo.Proc_GETCOUNTRY How It Works Once a stored procedure is dropped, its definition information is removed from the database’s system tables. Any cached query execution plans are also removed for that stored procedure. Code references to the stored procedure by other stored procedures or triggers will fail upon execution once the stored procedure has been dropped.

Creating a Parameterized Stored Procedure in Sql Server

In the previous recipe, I demonstrated a non-parameterized stored procedure, meaning that no external parameters were passed to it. The ability to pass parameters to them is part of why stored procedures are one of the most important database objects in SQL Server. Using parameters, you can pass information into the body of the procedure in order to return customized search information, or use parameters to influence or execute INSERT, UPDATE, or DELETE statements against tables. A procedure can have up to 2100 parameters (although it’s unlikely you’ll want to use nearly that many).

 

 

 

 

 

Example Parameterized Stored Procedure

CREATE PROCEDURE [dbo].[Proc_GETCOUNTRY]
(
@Country_id int
)
AS
BEGIN
SET NOCOUNT ON;
select Country from tbl_Country Where Country_id=@Country_id 
END

To get the result pass the value to procedure and get the result here how to pass the value in procedure.

EXEC Proc_GETCOUNTRY 3

Result is

Country

Brazil

Performing Data Manipulation Add and Update Rows

CREATE PROC [dbo]. Proc_SaveCountryName
(
@CountryName varchar(50)
)
AS
BEGIN
INSERT into dbo.tbl_Country( Country) VALUES ( @CountryName)
END

EXEC Proc_SaveCountryName 'England'

Result is

Country_id	Country
1	America
2	Canada
3	Brazil
4	Japan
5	India
6	China
7	England

Update rows using parameter values

CREATE PROC [dbo]. Proc_UpdateCountryName
(
@ID int,
@CountryName varchar(50)
)
AS
BEGIN
update dbo.tbl_Country SET country=@CountryName where Country_id=@ID
END

EXEC Proc_UpdateCountryName 3 ,'NewZealand'

Result is

Country_id	Country
1	America
2	Canada
3	NewZealand
4	Japan
5	India
6	China
7	England

How it works

In this Proc_SaveCountryName we have pass one parameter value to the procedure and it has saved the record in tbl_country table.
EXEC Proc_SaveCountryName 'England'
So In the next example we have updated the record with passing two paremeter value to the procedure like 
EXEC Proc_UpdateCountryName 3 ,'NewZealand'
so we have updated the 3rd row in table with 'NewZealand' Country name in place of'Brazil'.

Stored Procedure with Output Parameter in Sql Server

Output parameters enable a stored procedure to return data to the calling client procedure. The keyword output is required both when the procedure is created and when it is called. Within the stored procedure, the output parameter appears as a local variable. In the calling procedure or batch, a variable must have been created to receive the output parameter. When the stored procedure concludes, its current value is passed to the calling procedure’s local variable.

Output Parameter Example

ALTER PROCEDURE [dbo].[Proc_SaveCountry] 
( 
@Country nvarchar(30),
@Status tinyint output 
) 
AS BEGIN 
SET NOCOUNT ON; 
if exists (select Country_Id from tbl_Country where Country=@Country) 
Begin 
Set @Status=0 
end 
ELSE 
Begin 
insert into tbl_Country (Country) values (@Country) 
set @Status=1 
End 

END

In the Above Example this line means if exists (select Country_Id from tbl_Country where Country=@Country) 
Begin 
Set @Status=0 end 
It will check the country_id is already exist in database or not if not exists set @Status =0 means in front end coding by getting this value we can give the message that record already exists.
In the else part if country id is exists it will succesfully insert the record and set status =1 means record successfully inserted.

Using Output parameter Getting last Inserted Country_id value.

CREATE PROCEDURE [dbo].[Proc_SaveCountry] 
( 
@Country nvarchar(30),
@Country_id tinyint output 
) 
AS BEGIN 
SET NOCOUNT ON; 
insert into tbl_Country (Country) values (@Country) 
Select @Country_id =@@IDENTITY 
END

In this example the last inserted value in tbl_country is retreived by @@IDENTITY keyword and assign to output parameter @country_id

Fetch Stored Procedure

The fetch stored procedure retrieves the data. A sophisticated fetch procedure can accept various parameters and respond with a single row, filtered rows, or all rows, depending on the requirement and the parameters, so multiple fetch procedures are not necessary for various scopes of data. The null default is used in the where clause to effectively nullify the criterion if the parameter is not supplied.
This stored procedure also handles lock-timeout and deadlock issues using the techniques covered in the next chapter (we will disscuss it later), “Managing Transactions, Locking, and Blocking.” The following sample fetch stored procedure retrieves product information for the OBXKites database:

CREATE PROCEDURE pProduct_Fetch
(
@ProductCode CHAR(15) = NULL,
@ProductCategory CHAR(15) = NULL 
) AS
SET NoCount ON
SELECT Code, ProductName, ProductDescription, ActiveDate,
DiscontinueDate, ProductCategoryName, [RowVersion] --,
-- Product.Created, Product.Modified
FROM dbo.Product
JOIN dbo.ProductCategory
ON Product.ProductCategoryID
= ProductCategory.ProductCategoryID
WHERE ( Product.Code = @ProductCode
OR @ProductCode IS NULL )
AND ( ProductCategory.ProductCategoryName
= @ProductCategory
OR @ProductCategory IS NULL 
) IF @@Error <> 0 RETURN -100
RETURN

The following command executes the pProduct_Fetch stored procedure and retrieves data for all the products when called without any parameters:
EXEC pProduct_Fetch

 

 

 

Result (columns and rows abridged):

Code	Name	Modified
1001	Basic Box Kite 21 inch	2002-02-18 09:48:31.700
1002	Dragon Flight	2002-02-18 15:19:34.350
1003	Sky Dancer	2002-02-18 09:48:31.700

With a @ProductCode parameter, the fetch stored procedure returns only the selected product:
EXEC pProduct_Fetch
@ProductCode = ‘1005’

Result (columns abridged):

Code	Name	Modified
1005	Eagle Wings	2002-02-18 09:48:31.700

The second parameter causes the stored procedure to return all the products within a single product category:
EXEC pProduct_Fetch
@ProductCategory = ‘Book’

Result (rows and columns abridged):

Code	Name	Modified
1036	Adventures in the OuterBanks	2002-02-25 17:13:15.430
1037	Wright Brothers Kite Designs	2002-02-25 17:13:15.430
1038	The Lighthouses of the OBX	2002-02-25 17:13:15.430
1039	Outer Banks Map	2002-02-25 17:13:15.430
1040	Kiters Guide to the Outer Banks	2002-02-25 17:13:15.430

Update Stored Procedure in Sql Server

The update stored procedure accepts the primary method of identifying the row (in this case the product code) and the new data. Based on the new data, it performs a SQL DML update statement.

The previous example procedure handles lost updates by checking the rowversion timestamp column. Each time the row is updated, SQL Server automatically updates the rowversion value. If the rowversion is different, the row must have been updated by another transaction, and the rowversion condition in the where clause prevents the update.

Update with RowVersion

This version of the update procedure updates all the columns of the row, so all the parameters must be supplied even if that column is not being updated. The procedure assumes that the rowversion column was selected when the data was originally retrieved. If the rowversion value differs from the one retrieved during the select, the update fails to take place. The procedure determines that using the @@rowcount global variable and reports the error to the calling object. As a sample update procedure, here’s the code for the pProduct_Update_RowVersion stored procedure from the OBXKites database:

CREATE PROCEDURE pProduct_Update_RowVersion 
(
@Code CHAR(15),
@RowVersion Rowversion,
@Name VARCHAR(50),
@ProductDescription VARCHAR(50),
@ActiveDate DateTime,
@DiscontinueDate DateTime )
AS
SET NoCount ON
UPDATE dbo.Product
SET ProductName = @Name,
ProductDescription = @ProductDescription,
ActiveDate = @ActiveDate,DiscontinueDate = @DiscontinueDate
WHERE Code = @Code AND [RowVersion] = @RowVersion
IF @@ROWCOUNT = 0
BEGIN
IF EXISTS ( SELECT * FROM Product WHERE Code = @Code)
BEGIN
RAISERROR (‘Product failed to update because another transaction updated the row since your
last read.’, 16,1)
RETURN -100
END
ELSE
BEGIN
RAISERROR (‘Product failed to update because the row has been deleted’, 16,1)
RETURN -100
END
END
RETURN

To test the timestamp version of the update stored procedure, the pProduct_Fetch procedure will return the current timestamp for product code “1001”:
EXEC pProduct_Fetch 1001

Code	Name	RowVersion
1001	Basic Box Kite 21 inch	0x0000000000000077

The pProduct_Update_Rowversion stored procedure must be called with the exact same rowversion value to perform the update:
EXEC pProduct_Update_Rowversion
1001,0x0000000000000077,
‘updatetest’,‘new description’,‘1/1/2002’,NULL
The procedure updates all the columns in the row, and in the process, the rowversion column is reset to a new value.

Delete Stored Procedure in Sql Server

The delete stored procedure executes the delete DML command. This procedure can be the most complex stored procedure, depending upon the level of data-archival and logical deletion. This sample delete procedure taken from the OBXKites database transforms the @ProductCode into a @ProductID, verifies that the product does in fact exist, and then deletes it:

 

CREATE PROCEDURE pProduct_Delete
(
@ProductCode INT
)
AS
SET NOCOUNT ON
DECLARE @ProductID UniqueIdentifier
SELECT @ProductID = ProductID
FROM Product
WHERE Code = @ProductCode
If @@RowCount = 0
BEGIN
RAISERROR(‘Unable to delete Product Code %i - does not exist.’, 16,1, @ProductCode)
RETURN
END
ELSE
DELETE dbo.Product
WHERE ProductID = @ProductID
RETURN

To test the pProduct_Delete stored procedure, the following stored procedure attempts to call a product. Because there is no product code “99,” the error trapping raises the error: EXEC pProduct_Delete 99
Result:
Unable to delete Product Code 99 - does not exist.