SQL Stored Procedures

Why Stored Procedures

The mechanism that Dapper uses to process CRUD operations on a SQL database is by the use of SQL Stored Procedures. (CRUD = Create, Read, Update, Delete.) Basically there is a separate stored procedure for each CRUD operation for each table in the database.  (This is probably a simplified view and it's almost definitely possible that more complex stored procedures could be used to update multiple tables at the same time - but this is the simple version!)

The stored procedures created by Alan Simpson's code generation program will be the starting point.

Countries

Open SQL Management Studio, select the CountriesDb database, open a New Query window and paste in the following code.

-------------- Stored Proc for INSERT
CREATE PROCEDURE spCountries_Insert
--Parameters for Insert stored procedure
@CountryName varchar(50)
AS
BEGIN
--SQL for Insert stored procedure
INSERT INTO Countries(CountryName) VALUES (@CountryName)
END

GO

--------------  Stored Proc for SELECT (LIST, just first six fields but you can change in final code.)
CREATE PROCEDURE spCountries_GetAll
--No parameters required.
AS
BEGIN
--SQL for Select stored procedure.
SELECT CountryId, CountryName FROM Countries ORDER BY CountryId DESC
END

GO

--------------  Stored Proc for SELECT (one)
CREATE PROCEDURE spCountries_GetOne
-- Needs one parameter for primary key
@CountryId int
AS 
BEGIN
-- SQL Select for one table row
SELECT CountryId, CountryName FROM Countries WHERE CountryId= @CountryId
END

GO

--------------  Stored Proc for UPDATE
CREATE PROCEDURE spCountries_Update
-- Parameters for Update stored procedure.
@CountryId int,
@CountryName varchar(50)
AS
BEGIN
-- SQL for Update stored procedure
UPDATE Countries SET CountryName = @CountryName WHERE CountryId = @CountryId
END

GO

--------------  Stored Proc for DELETE
CREATE PROCEDURE spCountries_Delete
-- One parameter required to identify row to delete.
@CountryId int
AS
BEGIN
-- SQL for Delete stored procedure (physically deletes, you may want to change this to mark inactive)
DELETE FROM Countries WHERE CountryId = @CountryId
END

GO

I have slightly modified Alan Simpson's code by putting 'GO' statements after each section so that the whole script may be run as a whole.  I have also made other minor changes by re-naming spCountries_List to sp_Countries_GetAll and removing the 'Top 30' restriction, as well as removing the 'Search' stored procedure.

To run the query to create the stored procedures click the Execute button.

Close the Query window; there is no need to save the query

Cities

Repeat the process to create the stored procedures for the Cities table

-------------- Stored Proc for INSERT
CREATE PROCEDURE spCities_Insert
--Parameters for Insert stored procedure
@CityName varchar(50),
@CountryId int,
@CityPopulation int
AS
BEGIN
--SQL for Insert stored procedure
INSERT INTO Cities(CityName, CountryId, CityPopulation) VALUES (@CityName, @CountryId, @CityPopulation)
END

GO

--------------  Stored Proc for SELECT (LIST, just first six fields but you can change in final code.)
CREATE PROCEDURE spCities_GetAll
--No parameters required.
AS
BEGIN
--SQL for Select stored procedure.
SELECT CityId, CityName, CountryId, CityPopulation FROM Cities ORDER BY CityId DESC
END

GO

--------------  Stored Proc for SELECT (one)
CREATE PROCEDURE spCities_GetOne
-- Needs one parameter for primary key
@CityId int
AS 
BEGIN
-- SQL Select for one table row
SELECT CityId, CityName, CountryId, CityPopulation FROM Cities WHERE CityId= @CityId
END

GO

--------------  Stored Proc for UPDATE
CREATE PROCEDURE spCities_Update
-- Parameters for Update stored procedure.
@CityId int,
@CityName varchar(50),
@CountryId int,
@CityPopulation int
AS
BEGIN
-- SQL for Update stored procedure
UPDATE Cities SET CityName = @CityName, CountryId = @CountryId, CityPopulation = @CityPopulation WHERE CityId = @CityId
END

GO

--------------  Stored Proc for DELETE
CREATE PROCEDURE spCities_Delete
-- One parameter required to identify row to delete.
@CityId int
AS
BEGIN
-- SQL for Delete stored procedure (physically deletes, you may want to change this to mark inactive)
DELETE FROM Cities WHERE CityId = @CityId
END

GO

Confirm the stored procedures have been created by refreshing the CountriesDb database (by right-clicking on the database name and clicking 'Refresh') , selecting 'Programmability' and expanding 'Stored Procedures'.  The newly created stored procedures should be displayed.